CASE WHEN语句中MAX函数用法解析：代码中MAX如何匹配各职业对应行

问题背景 #

在以下SQL代码中，针对Name字段使用MAX函数时，它是如何筛选出各职业对应行的？

WITH RankedOccupations AS (
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
    FROM OCCUPATIONS
)
SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor
FROM RankedOccupations
GROUP BY RowNum;

分步解析 #

1. CTE的行号标记作用
   首先看RankedOccupations这个公用表表达式：

• 用PARTITION BY Occupation把全表数据按职业分组，比如所有医生归为一组，所有教授归为另一组。
• 再用ROW_NUMBER() OVER (...)给每个职业组内的名字按字母顺序编号（ORDER BY Name）。也就是说，每个职业里的第一个名字行号是1，第二个是2，以此类推。

2. 按行号分组的逻辑
   主查询里用GROUP BY RowNum，把所有行号相同的记录归为一组。比如行号为1的组里，会包含医生组的第1个名字、教授组的第1个名字（如果两个职业都有对应行号的记录）。

3. CASE语句的取值逻辑
   以MAX(CASE WHEN Occupation = 'Doctor' THEN Name END)为例：

• 当记录的职业是Doctor时，返回对应的Name；否则返回NULL。
• 在同一个行号组里，只有职业为Doctor的那条记录会返回Name，其他记录都是NULL。

4. MAX函数的筛选作用
   MAX函数的特性是忽略NULL值，只取组内非NULL的那个值。所以在同一个行号组里：

• Doctor列的MAX值就是该组里唯一的非NULL医生名字（也就是对应职业组里第N个名字，N是当前行号）。
• Professor列的MAX值同理，取该组里唯一的非NULL教授名字。

最终通过这种方式，把不同职业的名字按排序后的顺序逐行对齐，转换成列的形式展示。

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