查询2016欧洲杯输出球员最多俱乐部的嵌套SQL报错原因咨询

原SQL错误原因 #

你写的SQL存在两个核心问题，导致无法得到预期结果甚至直接报错：

• 外层逻辑冗余：子查询已经按playing_club分组统计出了每个俱乐部的输送球员数，每条记录对应唯一俱乐部，外层再按playing_club分组后求MAX(NumberOfPlayerForTeam)，本质就是返回每个俱乐部自身的统计人数，完全达不到筛选出最大值的目的
• 语法不规范：绝大多数数据库要求FROM后的子查询必须设置别名，这也是SQL直接运行报错的常见原因

正确实现代码 #

通用写法（适配所有主流数据库，支持返回多个并列第一的俱乐部） #

SELECT playing_club, NumberOfPlayerForTeam
FROM (
  SELECT playing_club, COUNT(player_id) AS NumberOfPlayerForTeam
  FROM player_mast
  GROUP BY playing_club
) AS club_stat
WHERE NumberOfPlayerForTeam = (
  SELECT MAX(cnt) 
  FROM (
    SELECT COUNT(player_id) AS cnt
    FROM player_mast
    GROUP BY playing_club
  ) AS t
);

窗口函数写法（适配MySQL8.0+、PostgreSQL、Oracle等支持窗口函数的数据库） #

SELECT playing_club, NumberOfPlayerForTeam
FROM (
  SELECT 
    playing_club,
    COUNT(player_id) AS NumberOfPlayerForTeam,
    RANK() OVER(ORDER BY COUNT(player_id) DESC) AS ranking
  FROM player_mast
  GROUP BY playing_club
) AS t
WHERE ranking = 1;

不需要处理并列场景的极简写法 #

如果无需保留并列第一的所有俱乐部，只要返回任意一个输送球员最多的俱乐部，可以直接排序后取第一条：

SELECT playing_club, COUNT(player_id) AS NumberOfPlayerForTeam
FROM player_mast
GROUP BY playing_club
ORDER BY NumberOfPlayerForTeam DESC
LIMIT 1;

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