如何按位置进一步拆分SQL透视查询（NFL球员数据场景）

错误原因分析 #

• PIVOT语法错误：FOR(collegeName) 里的括号属于多余写法，不符合SQL PIVOT的语法规范。
• 列名未转义：IN子句中的大学名称（比如Florida State带空格）没有用标识符包裹，数据库无法识别为合法列名。
• 结果集未指定别名：部分SQL方言（如SQL Server）要求PIVOT生成的结果集必须指定别名。

修正后的SQL代码（支持PIVOT的数据库，如SQL Server、Oracle） #

SELECT * 
FROM (
    SELECT collegeName, position
    FROM NFL.Players
) AS PlayerData
PIVOT (
    COUNT(*)  -- 用COUNT(*)统计所有行，避免position为NULL时漏统计
    FOR collegeName IN (
        [Georgia], 
        [Alabama], 
        [Florida State], 
        [Texas Tech], 
        [Texas], 
        [Michigan], 
        [Louisiana State]
    )
) AS PositionCollegePivot
ORDER BY position;

适配MySQL的写法（MySQL无原生PIVOT，用条件聚合实现） #

如果你的数据库是MySQL，直接用条件聚合更稳妥：

SELECT 
    position,
    COUNT(CASE WHEN collegeName = 'Georgia' THEN 1 END) AS Georgia,
    COUNT(CASE WHEN collegeName = 'Alabama' THEN 1 END) AS Alabama,
    COUNT(CASE WHEN collegeName = 'Florida State' THEN 1 END) AS `Florida State`,
    COUNT(CASE WHEN collegeName = 'Texas Tech' THEN 1 END) AS `Texas Tech`,
    COUNT(CASE WHEN collegeName = 'Texas' THEN 1 END) AS Texas,
    COUNT(CASE WHEN collegeName = 'Michigan' THEN 1 END) AS Michigan,
    COUNT(CASE WHEN collegeName = 'Louisiana State' THEN 1 END) AS `Louisiana State`
FROM NFL.Players
GROUP BY position
ORDER BY position;

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