SQL中Partition by子句为何会对PIVOT查询结果产生重大影响？

问题原因解析 #

核心是*ROW_NUMBER()的计算逻辑变化 + PIVOT的隐含分组规则*共同导致的： #

1. 原带PARTITION BY occupation的逻辑 #

你子查询里的开窗函数ROW_NUMBER() OVER ( partition by occupation ORDER BY name_) rn作用是按职业分组，在每个职业内部按名字排序生成序号：

• Doctor职业共2人，生成rn为1、2
• Professor职业共3人，生成rn为1、2、3
• Singer职业共2人，生成rn为1、2
• Actor职业共3人，生成rn为1、2、3

SQL的PIVOT操作有固定隐含规则：会自动把子查询中「除了聚合用的列、FOR子句指定的转列字段」之外的所有列，作为分组依据。这里子查询输出的列是rn、name_、Occupation，所以PIVOT自动按rn分组：
同一个rn的行里，会分别取对应职业的名字做聚合，所以最终只生成3行（所有职业里最大rn为3），每行对应同一个序号下四个职业的姓名，没有对应数据的位置显示null，就是你最初拿到的正常结果。 #

2. 去掉PARTITION BY occupation后的逻辑 #

去掉分区规则后，ROW_NUMBER() OVER (ORDER BY name_) rn变成全局按名字排序生成唯一序号，10条源数据的rn就是互不重复的1~10。
此时PIVOT还是按rn分组，每个分组里只有1条数据，对应唯一的一个职业：
比如rn=1的数据是Ashley，职业是Professor，所以这一行只有Professor列有值，其他三列都是null；10个不同的rn就生成10行，每行仅对应一个职业的非null值，就是你看到的「异常」结果。

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