SQL Server查询每日最大小时用水量及对应时间的实现问题

嗨，看起来你已经走完大半流程啦，问题出在最后关联的时候没处理好子查询的引用关系。其实不用嵌套多层子查询再硬关联，用窗口函数能更简洁清晰地解决这个问题。

先理清楚你的核心需求：从Water表中，按天分组，找出每天用水量最高的那个小时，同时要显示该小时的具体时间time_local、日期date_local以及对应的总用水量。

解决方案：使用ROW_NUMBER()窗口函数 #

窗口函数可以帮我们在每个日期分组内，对小时用水量进行排序，直接取排序第一的记录（也就是当天的最大值条目）。具体SQL如下：

WITH HourlyUsage AS (
    -- 第一步：先计算每天每个小时的总用水量，和你之前的子查询t1逻辑一致
    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
),
RankedUsage AS (
    -- 第二步：给每个日期的小时用水量按从大到小排序，最大值排第1
    SELECT 
        Gallons,
        time_local,
        date_local,
        ROW_NUMBER() OVER (PARTITION BY date_local ORDER BY Gallons DESC) AS UsageRank
    FROM HourlyUsage
)
-- 第三步：筛选出每个日期排名第1的记录，就是我们要的结果
SELECT 
    Gallons,
    time_local,
    date_local
FROM RankedUsage
WHERE UsageRank = 1;

为什么这个方法更高效？ #

• HourlyUsage是公共表表达式（CTE），作用和你之前的子查询t1一样，负责计算每个小时的总用水量。
• RankedUsage里的ROW_NUMBER() OVER (PARTITION BY date_local ORDER BY Gallons DESC)会把同一date_local的记录归为一组，每组内按Gallons从大到小排序，给每条记录分配一个排名。当天用水量最大的小时会拿到排名1。
• 最后只需要筛选出UsageRank = 1的记录，就能直接得到每天最大用水量及其对应的时间。

补充：处理同一天多个并列最大值的情况 #

如果某天有两个小时的总用水量完全相同，且都是当天的最大值，上面的ROW_NUMBER()只会返回其中一条。如果你想把所有并列最大值的记录都展示出来，可以把ROW_NUMBER()换成RANK()或者DENSE_RANK()——这两个函数会给相同数值的记录分配相同的排名。

关于你之前关联方法出错的原因 #

你之前的写法里，外层查询只能直接引用最外层的子查询t2，而t1是t2的内部子查询，外层无法直接访问t1的字段。如果一定要用关联的方式实现，需要把t1和t2放在同一层级关联，比如：

SELECT t1.Gallons, t1.time_local, t1.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
JOIN (
    SELECT MAX(Gallons) AS MaxHour, date_local
    FROM (
        SELECT SUM(consumption) AS Gallons, date_local
        FROM water
        WHERE consumption_period = 'HOUR' AND date_local > DATEADD(day,-2, GETDATE())
        GROUP BY time_local, date_local
    ) AS t
    GROUP BY date_local
) AS t2 ON t1.date_local = t2.date_local AND t1.Gallons = t2.MaxHour;

不过这种写法嵌套了三层，可读性和维护性都不如窗口函数的方案，所以更推荐第一种实现方式。

备注：内容来源于stack exchange，提问作者Stedman