如何对GROUP BY列表外的列执行聚合操作？附SQL示例

嘿，这个问题其实挺常见的——当你用GROUP BY对某列（比如这里的day）分组后，想拿到分组里对应聚合值（比如max(val)）的其他关联列（比如dt），对吧？我给你几种实用的解决方法，全部用你提供的示例表来演示：

方法1：使用窗口函数（最灵活推荐） #

窗口函数可以在不提前分组的前提下，给每个分组内的行标记排序，这样就能轻松拿到对应聚合值的完整行。常用的是ROW_NUMBER()，如果有多个相同最大值的行，也可以用RANK()/DENSE_RANK()保留所有结果：

declare @table table(val int, dt datetime) 
insert into @table values 
(10, '2018-3-20 16:00'), 
(12, '2018-3-20 14:00'), 
(14, '2018-3-20 12:00'), 
(16, '2018-3-20 10:00'), 
(10, '2018-3-19 14:00'), 
(12, '2018-3-19 12:00'), 
(14, '2018-3-19 10:00'), 
(10, '2018-3-18 12:00'), 
(12, '2018-3-18 10:00');

WITH ranked_data AS (
    SELECT 
        DATEPART(DAY, dt) AS day,
        val,
        dt,
        -- 按天分组，每组内按val降序排，标记行号
        ROW_NUMBER() OVER(PARTITION BY DATEPART(DAY, dt) ORDER BY val DESC) AS rn
    FROM @table
)
SELECT day, val AS max_by_value, dt AS max_val_datetime
FROM ranked_data
WHERE rn = 1; -- 取每组的第一行（val最大的行）

如果同一天存在多个相同的max(val)，ROW_NUMBER()会随机返回其中一行；换成RANK()则会返回所有最大值对应的行。

方法2：聚合子查询+关联匹配 #

先通过GROUP BY拿到每天的max(val)，再关联原表找到对应的dt列：

-- 先分组得到每天的max(val)，再关联原表获取对应dt
SELECT 
    q.day,
    q.max_by_value,
    t.dt
FROM (
    SELECT DATEPART(DAY, dt) AS day, MAX(val) AS max_by_value 
    FROM @table 
    GROUP BY DATEPART(DAY, dt)
) q
JOIN @table t ON DATEPART(DAY, t.dt) = q.day AND t.val = q.max_by_value;

这种方法逻辑直观，但如果同一天有多个相同的max(val)，会返回多行结果；若想只保留唯一行，可以在SELECT后加DISTINCT。

方法3：使用APPLY操作符（SQL Server专属） #

APPLY相当于给每个分组单独执行一次子查询，适合需要灵活筛选分组内数据的场景：

-- 使用CROSS APPLY获取每个day的max(val)对应行
SELECT 
    days.day,
    max_val.val AS max_by_value,
    max_val.dt
FROM (SELECT DISTINCT DATEPART(DAY, dt) AS day FROM @table) days
CROSS APPLY (
    SELECT TOP 1 val, dt 
    FROM @table 
    WHERE DATEPART(DAY, dt) = days.day 
    ORDER BY val DESC
) max_val;

如果要保留所有最大值的行，把TOP 1换成TOP 1 WITH TIES即可。

内容的提问来源于stack exchange，提问作者Serg046