MySQL表转置（Pivot）后NULL值前置问题及正确输出实现求助

需求回顾 #

需要将OCCUPATIONS表按职业转置，输出列头依次为Doctor、Professor、Singer、Actor；每个职业对应的Name按字母顺序排序后显示在对应列下，无对应名称时用NULL填充，且NULL需显示在列的末尾而非开头。

原查询的问题 #

你之前的查询通过CASE WHEN生成单职业单列的行数据，直接排序时，多数SQL引擎会把NULL的排序优先级设为低于非空值，导致所有NULL行集中在结果开头，无法实现不同职业同位置名称的对齐。

解决方案 #

核心思路是先给每个职业内的名称按字母顺序分配行号，再通过行号将不同职业的同位置名称聚合到同一行，自然实现非空值在前、NULL在后的效果。

通用SQL写法（适用于MySQL、PostgreSQL、SQL Server等） #

SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
    MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer,
    MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor
FROM (
    -- 子查询：给每个职业下的名称按字母排序分配行号
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
    FROM OCCUPATIONS
) AS ranked_data
GROUP BY rn
ORDER BY rn;

支持PIVOT语法的写法（如SQL Server） #

如果你的数据库支持PIVOT语法，也可以用更简洁的写法：

SELECT Doctor, Professor, Singer, Actor
FROM (
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
    FROM OCCUPATIONS
) AS ranked_data
PIVOT (
    MAX(Name)
    FOR Occupation IN (Doctor, Professor, Singer, Actor)
) AS pivoted_result
ORDER BY rn;

代码说明 #

1. 子查询部分：用ROW_NUMBER()窗口函数，按Occupation分组（PARTITION BY Occupation），并按Name字母顺序排序（ORDER BY Name），给每个职业下的名称分配唯一行号rn。比如Doctor职业的第一个名称rn=1，第二个rn=2，以此类推。
2. 外层聚合/转置：按行号rn分组，通过MAX(CASE ...)或PIVOT提取同一行号下不同职业的名称。同一rn下每个职业最多只有一个非空名称，聚合函数会自动忽略NULL，没有对应名称的位置则保留NULL。
3. 排序：最后按rn排序，确保各行的顺序是名称排序后的位置，非空值自然排在列的前面，NULL则补在列的末尾。

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