多GROUP BY子句下SQL中SUM与MAX函数联用问题排查与解决

现有学生成绩表如下：

需求：找出每个周期中总分最高的学生，若有多个学生同分，需全部列出。

错误查询分析 #

第一个查询报错原因 #

执行以下SQL时触发错误：

SELECT
    MAX(SUM(point)) score,
    student,
    `period`
FROM table1
GROUP BY student, `period`

错误信息：

#1111 - Invalid use of group function

原因：SQL不允许在同一SELECT层级嵌套聚合函数（如MAX(SUM(...))），聚合函数只能作用于已分组的结果集，不能直接嵌套使用。

第二个查询的问题 #

执行嵌套查询后：

SELECT
    `period`,
    student,
    MAX(p) score
FROM
(
    SELECT
        SUM(point) p,
        student,
        `period`
    FROM table1
    GROUP BY student, `period`
) t1
GROUP BY `period`

结果中分数正确，但学生ID仅显示每个周期的首个学生，不符合需求。

原因：GROUP BY period时，student字段既不在聚合函数中也不在GROUP BY子句（仅MySQL非严格模式允许此写法），此时MySQL会随机选择分组内的一个student值，无法覆盖所有同分的最高分学生。

正确解法 #

方法1：窗口函数（MySQL 8.0+ 支持） #

利用窗口函数实现分组排名，筛选每个周期的最高分学生：

SELECT period, student_id, total_score
FROM (
    SELECT 
        `period`,
        student AS student_id,
        SUM(score) AS total_score,
        RANK() OVER (PARTITION BY `period` ORDER BY SUM(score) DESC) AS rnk
    FROM table1
    GROUP BY student, `period`
) t
WHERE rnk = 1;

说明：

• PARTITION BY period：按周期分组处理
• RANK()：对每个周期内的学生总分降序排名，同分学生共享同一排名
• 筛选rnk=1即可获取每个周期的所有最高分学生

方法2：子查询关联（兼容低版本MySQL） #

若不支持窗口函数，可先计算各周期最高总分，再关联匹配学生记录：

SELECT 
    t1.`period`,
    t1.student AS student_id,
    t1.total_score
FROM (
    SELECT 
        student,
        `period`,
        SUM(score) AS total_score
    FROM table1
    GROUP BY student, `period`
) t1
JOIN (
    SELECT 
        `period`,
        MAX(total_score) AS max_score
    FROM (
        SELECT 
            `period`,
            SUM(score) AS total_score
        FROM table1
        GROUP BY student, `period`
    ) t2
    GROUP BY `period`
) t3 ON t1.`period` = t3.`period` AND t1.total_score = t3.max_score;

说明：

1. 子查询t1计算每个学生在各周期的总分
2. 子查询t3计算每个周期的最高总分
3. 通过JOIN关联，筛选出总分等于对应周期最高分的学生记录

预期结果 #

执行正确查询后，将得到符合需求的结果：

（若存在同分学生，结果会全部列出，比如某周期有两名学生总分同为最高，则显示两行记录）

内容的提问来源于stack exchange，提问作者Simon-Varga Csaba