MAX(CASE WHEN ... END)表达式工作原理及必要性详解

先看示例查询：

SELECT MAX(CASE WHEN occupation = 'doctor' THEN NAME ELSE NULL END) AS doctor,
       MAX(CASE WHEN occupation = 'professor' THEN NAME ELSE NULL END) AS professor,
       MAX(CASE WHEN occupation = 'singer' THEN NAME ELSE NULL END) AS singer,
       MAX(CASE WHEN occupation = 'actor' THEN NAME ELSE NULL END) AS actor
FROM (SELECT name,
             occupation,
             ROW_NUMBER() OVER (PARTITION BY occupation
                                ORDER BY name) AS rowno
      FROM occupations) subtb
GROUP BY rowno
ORDER BY rowno;

我已经理解这段查询里的子查询部分——它按职业分组，给每个职业下的姓名按字母顺序分配行号，生成结果表如下：

但我搞不懂外层查询里的MAX(CASE WHEN ...)是怎么把上面的表转换成目标结果表的，而且我原本以为只用CASE WHEN就能完成转换，但去掉MAX后查询根本无法得到正确结果。目标结果表如下：

为什么单独用CASE WHEN不行？ #

如果去掉MAX，只保留CASE WHEN，同时按rowno分组的话，每个rowno分组里会有多条记录（比如rowno=1的分组里有4条记录）。分组查询要求每个分组只能返回一行结果，但此时每个字段会对应多条不同的CASE结果，数据库无法确定要取哪一条，要么报错，要么返回混乱的结果，根本达不到列转行的效果。

MAX(CASE WHEN ...)的具体工作逻辑 #

我们以rowno=1的分组为例拆解：

1. 分组内的4条记录分别代入每个CASE WHEN表达式：
   • doctor列：只有Aamina的职业是doctor，所以这条记录返回Aamina，其余3条返回NULL
   • professor列：只有Ashley的职业是professor，返回Ashley，其余返回NULL
   • singer列：只有Christeen的职业是singer，返回Christeen，其余返回NULL
   • actor列：只有Eve的职业是actor，返回Eve，其余返回NULL
2. 对每个列的结果执行MAX聚合：因为MAX会忽略NULL值，而每个列的分组结果里只有一个非NULL值，所以直接返回这个值。
3. 最终rowno=1的分组生成一行数据，刚好对应目标表的第一行。

再看rowno=4的分组：

• doctor列：没有rowno=4的医生记录，所有CASE结果都是NULL，MAX后返回NULL
• professor列：Maria的rowno是4，返回Maria，其余是NULL，MAX后返回Maria
• singer列：Kristeen的rowno是4，返回Kristeen，其余是NULL，MAX后返回Kristeen
• actor列：Samantha的rowno是4，返回Samantha，其余是NULL，MAX后返回Samantha
  最终生成目标表的第四行数据。

核心作用总结 #

• CASE WHEN负责行转列标记：把每行的姓名放到对应职业的列中，不符合条件的标记为NULL
• MAX负责聚合筛选：在同一个rowno分组里，提取对应职业列中唯一的非NULL值，同时忽略NULL，保证每个分组只返回一行数据，完美匹配目标表的结构。

其实这里换成MIN也能得到相同结果，因为每个分组的对应列里只有一个非NULL值，MIN和MAX都会返回这个值。

内容的提问来源于stack exchange，提问作者Purev-Ochir Lkhamsuren