我有一个SQL服务器表‘Water’,它按每个米存储每小时的用水量.我正在try 汇总所有使用情况,以确定每天的最大使用小时数以及它发生的时间.我遇到的问题是如何 for each MAX记录返回‘TIME_LOCAL’.
我最初的查询总和是每天每个小时的所有使用量.
SELECT SUM(consumption) AS Gallons,
time_local,
date_local
FROM water
WHERE consumption_period = 'HOUR' AND date_local > DATEADD(day,-2, GETDATE())
GROUP BY
time_local,
date_local
它返回一天中每小时的总消耗量.
gallons | time_local | date_local |
---|---|---|
275,009 | 2023-12-19T15:00:00 | 2023-12-19 |
184,074 | 2023-12-19T06:00:00 | 2023-12-19 |
392,489 | 2023-12-18T12:00:00 | 2023-12-18 |
然后,我使用初始语句作为子查询获取每一天的最大值
SELECT *
FROM
(SELECT
MAX(gallons) AS MaxHour,
date_local
FROM
(SELECT SUM(consumption) AS Gallons,
time_local,
date_local
FROM water
WHERE consumption_period = 'HOUR' AND date_local > DATEADD(day,-2, GETDATE())
GROUP BY
time_local,
date_local) AS t1
GROUP BY date_local) AS t2
这为我提供了每天的最大加仑汽油.
gallons | date_local |
---|---|
392,489 | 2023-12-18 |
315,744 | 2023-12-19 |
我正在解决的问题是将所有这些放在一起,这样我就可以报告每个Max值发生的‘time_local’,从而得到这样的结果.
gallons | time_local | date_local |
---|---|---|
392,489 | 2023-12-18T12:00:00 | 2023-12-18 |
315,744 | 2023-12-19T18:00:00 | 2023-12-19 |
我曾try 连接t1加仑=t2.max小时,但我做错了一些事情,因为系统告诉我‘t1不存在’.