PostgreSQL中GROUP BY查询同时展示行数据与列聚合值的实现

场景与需求 #

现有students表数据：

students:
    id  score  section    ...
    --------------------------------------------
    1   85     A        ...
    2   40     A        ...
    3   61     B        ...
    4   71     B        ...
    5   80     B        ...  

需要实现：查询每个学生的完整数据，同时显示该学生所在section的最高分，期望结果：

students:
    id  score  section  section_max_score  ...
    --------------------------------------------
    1   85     A        85                 ...
    2   40     A        85                 ...
    3   61     B        80                 ...
    4   71     B        80                 ...
    5   80     B        80                 ...

错误尝试分析 #

第一种SQL报错原因 #

执行语句：

select id, score, section, max(score) as section_max_score from students group by section;

报错要求将其他列加入GROUP BY：
这是因为SQL标准（以及开启严格模式的数据库）规定：GROUP BY子句必须包含所有非聚合列。这里id、score属于非聚合列（未用聚合函数包裹），但未出现在GROUP BY中，数据库无法确定要从分组里选取哪一行的id和score返回，因此报错。

第二种SQL结果不符原因 #

执行语句：

select id, score, section, max(score) as section_max_score from students group by section, score, id;

结果仅显示学生个人分数：
因为id是唯一标识，将id加入GROUP BY后，每个分组就对应单个学生（每个分组只有一行数据），此时max(score)计算的就是该学生自己的分数，自然无法得到整个section的最高分。

正确解法 #

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

窗口函数可以在保留原始行数据的同时，计算分组的聚合值，语法简洁高效：

SELECT 
    id, 
    score, 
    section, 
    MAX(score) OVER (PARTITION BY section) AS section_max_score
FROM students;

• PARTITION BY section：将数据按section分组
• MAX(score) OVER (...)：为每一行计算其所在分组的最高分，最终每一行都会保留原始数据，同时带上对应分组的最高分。

方法2：子查询关联（兼容老版本数据库） #

如果数据库不支持窗口函数，可以先通过子查询算出每个section的最高分，再关联原表获取完整数据：

SELECT 
    s.id, 
    s.score, 
    s.section, 
    t.section_max_score
FROM students s
INNER JOIN (
    SELECT section, MAX(score) AS section_max_score
    FROM students
    GROUP BY section
) t ON s.section = t.section;

• 子查询部分先按section分组，计算每个组的最高分
• 通过INNER JOIN将原表数据与子查询结果按section关联，为每个学生匹配对应班级的最高分。

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