SQL行转列中CASE搭配MAX、GROUP BY的执行原理咨询

你遇到的是经典的SQL行转列（透视表）场景，两个疑问的底层逻辑如下：

MAX+GROUP BY实现行合并的执行逻辑 #

整个计算过程分两步走：

• 第一步执行分组：GROUP BY student_id会先扫描全表，把所有student_id相同的记录归到同一个分组。样例数据会被拆成两个分组：学生01的3条成绩为一组，学生02的2条成绩为一组，每个分组最终只会输出1行结果。
• 第二步逐组逐列做聚合计算：这里用MAX不是为了求单科最高分，完全是利用了SQL所有聚合函数默认忽略NULL值的特性：
  • 计算学生01分组的01科目列时，3行记录对应的CASE表达式结果分别是80、NULL、NULL，MAX会自动跳过两个NULL，取到唯一的非NULL值80；
  • 计算02科目列时，3行CASE结果是NULL、90、NULL，MAX取到90；
  • 计算03科目列时，3行CASE结果是NULL、NULL、99，MAX取到99；
  • 计算学生02分组的01科目列时，组内2行记录的CASE结果全是NULL，MAX找不到非NULL值就会返回NULL，正好匹配“无对应成绩显示NULL”的需求。

补充：只要是会忽略NULL的聚合函数在这里都能生效，比如MIN、SUM（前提是每个学生每科最多1条成绩记录），写MAX只是行业通用习惯。

不写GROUP BY的异常原因，以及GROUP BY的作用 #

你看到的“不写GROUP BY会排除有NULL成绩的学生”只是表面现象，本质是SQL的聚合执行规则限制：

• 如果SELECT语句里同时存在未包裹聚合函数的普通列（比如直接写的student_id）和聚合计算列（比如MAX(...)），又没写GROUP BY指定分组维度，属于不符合SQL标准的写法：
  • 在PostgreSQL、SQL Server、开启only_full_group_by模式的MySQL等绝大多数数据库里，这种写法直接报语法错误，根本无法执行；
  • 在旧版MySQL这类允许非标准语法的环境里，数据库会把整张表的所有行当成唯一的一个大分组做聚合，最终只会返回1行全局计算的结果，根本不会按学生拆分成多行，自然得不到你要的效果。
• GROUP BY student_id的核心作用是明确聚合计算的粒度：告诉数据库要按每个独立的student_id单独做分组聚合，只要某个student_id在原表存在记录，就一定会生成对应的结果行，哪怕这个学生某科的CASE结果全是NULL，聚合后也会正常返回NULL，不会漏掉缺成绩的学生记录。

代码笔误提醒：你贴的参考代码有两个会导致执行报错的小问题：一是表名写了SQL保留字table，需要替换成实际的表名score；二是最后一个MAX计算列后面多了冗余的逗号，需要删掉。修正后可直接执行的代码如下：

SELECT 
    student_id, 
    MAX(CASE WHEN course_id = '01' THEN score ELSE NULL END) AS '01', 
    MAX(CASE WHEN course_id = '02' THEN score ELSE NULL END) AS '02', 
    MAX(CASE WHEN course_id = '03' THEN score ELSE NULL END) AS '03'
FROM score
GROUP BY student_id; 

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