MS 2016查询范围与平均每日查询范围比较

Question

我想在Microsoft 2016中做一个查询，显示每天的范围(最大-最低)和平均每天。

我已经完成了一个查询，它显示了"16-Jun-16“和”17-Jun-16“之间所有记录的结果：

SELECT [Date Time], [Operation Grade-Load], [MFR g/10min]
FROM [In process analysis result]
WHERE ([Date Time] Between Date()-360 And Date()-358)
AND ([Operation Grade-Load] Like "MH*");

但是，我不知道如何继续修改我的查询以显示每天的“范围”和“平均值”。我想我应该使用Avg()，Max()-Min()函数。有人能指点我吗？

编辑:每天根据前一天02:00:00和今天02:00:00之间计算。示例：

• 16-6月-16=“16-6月-16 02:00”和“17-6月-16 02:00:00”之间的记录
• 17-6月16日=“17-6月-16 02:00”和“18-6月-16 02:00:00”之间的记录
• ..。

是否有可能根据这一逻辑计算日期？

任何帮助都是非常感谢的。谢谢。

​

​

编辑1:我更新了SQL查询：

SELECT [Date Time], [Range], [Average]
FROM [In process analysis result], (SELECT Max([MFR g/10min])-Min([MFR g/10min]) AS [Range], Avg([MFR g/10min]) AS Average
FROM [In process analysis result]
WHERE ((([In process analysis result].[Date Time]) Between Date()-360 And Date()-358) AND (([In process analysis result].[Operation Grade-Load]) Like "MH*"))
)  AS [%$##@_Alias]
WHERE ((([In process analysis result].[Date Time]) Between Date()-360 And Date()-358) AND (([In process analysis result].[Operation Grade-Load]) Like "MH*"))
ORDER BY [In process analysis result].[Date Time];

结果很接近我想要的.但现在还没有，因为它不是根据每天计算的。

​

​

编辑2:这是预期的结果：

​

​

编辑3:在@USeptim之后，这是创建的查询的结果。然而，它创建了另两列的范围和PromedioDeMFR g/10分钟。

​

​

Answer 1

你可以这样做：

SELECT [In process analysis result].[Date Time], [In process analysis 
result].[MFR g/10min], QueryAux.Range, QueryAux.[PromedioDeMFR g/10min]
FROM [In process analysis result] INNER JOIN 
(SELECT Max([MFR g/10min])-Min([MFR g/10min]) AS Range, Avg([In process 
analysis result].[MFR g/10min]) AS [PromedioDeMFR g/10min], [In process 
analysis result].[Date Time], [In process analysis result].[Operation Grade-
Load]
FROM [In process analysis result]
GROUP BY [In process analysis result].[Date Time], [In process analysis 
result].[Operation Grade-Load]) QueryAux 
ON ([In process analysis result].[Operation Grade-Load] = QueryAux.
[Operation Grade-Load]) AND ([In process analysis result].[Date Time] = 
QueryAux.[Date Time])
WHERE ((([In process analysis result].[Date Time]) Between Date()-360 And 
Date()-358) AND (([In process analysis result].[Operation Grade-Load]) Like 
"MH*"))
ORDER BY [In process analysis result].[Date Time];

您可以使用我称为"query“的子查询创建一个查询，并直接引用它。