SQLite中如何编写正确SQL查询实现符合预期的球员数据透视表

原代码错误点 #

• CTE中错误添加GROUP BY nationality逻辑，会直接丢弃同国籍下不同球员的等级明细，且违反SQL分组语法规则（非聚合字段未包含在GROUP BY子句中），无法统计到同国籍同等级的多名球员数量
• 条件匹配的等级值与表中实际存储值不一致：原代码使用Wereldklasse/Topklasse/Subtop作为匹配值，实际表中Class字段存储值为World_class/Top_class/Subtop_class
• 聚合逻辑错误：SUM函数内传入字符串类型的等级字段，无法实现人数计数的目标

修正后SQL代码 #

SELECT
  nationality,
  COALESCE(SUM(CASE WHEN Class = 'World_class' THEN 1 END), 0) AS World_class,
  COALESCE(SUM(CASE WHEN Class = 'Top_class' THEN 1 END), 0) AS Top_class,
  COALESCE(SUM(CASE WHEN Class = 'Subtop_class' THEN 1 END), 0) AS Subtop_class
FROM Fifaklasse
GROUP BY nationality;

逻辑说明 #

• 移除了存在逻辑错误的冗余CTE，直接从源表按国籍分组统计
• 当CASE语句匹配到对应等级的球员时返回数值1，通过SUM累加得到对应等级的球员总人数
• 使用COALESCE函数将无对应等级球员的分组空值转换为0，输出格式与预期完全一致
  该写法为通用SQL语法，可在MySQL、PostgreSQL、SQL Server等绝大多数主流关系型数据库中直接运行，无需适配数据库专属的PIVOT函数。

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