Oracle结合子查询与聚合函数查询中场球员最多的世界杯国家

先分析你两个查询的问题 #

• 第一个查询：外层GROUP BY nationname统计的是该国所有位置的球员总数，但子查询统计的是各国中场球员数的最大值，两者统计维度完全不同，所以永远匹配不到结果，自然无输出。另外DISTINCT是多余的，GROUP BY已经确保每行对应唯一国家。
• 第二个查询：外层GROUP BY nationname后，MAX(midfield)本质就是子查询里每个国家自身的中场数，相当于没做筛选，只会返回所有国家的中场球员数，无法定位最大值对应的国家。

两种可行的Oracle实现方案 #

方案1：先统计中场数，再匹配最大值 #

先用子查询统计每个国家的中场球员数，再找出这个统计结果里的最大值，最后筛选出数值等于最大值的国家：

WITH nation_midfield AS (
    SELECT nationname, COUNT(*) AS midfield_count
    FROM Player
    WHERE Role = 'midfield'
    GROUP BY nationname
)
SELECT nationname, midfield_count
FROM nation_midfield
WHERE midfield_count = (SELECT MAX(midfield_count) FROM nation_midfield);

方案2：用窗口函数处理（支持并列情况） #

如果存在多个国家中场球员数相同且都是最多的情况，用RANK()窗口函数可以一次性返回所有并列第一的国家：

SELECT nationname, midfield_count
FROM (
    SELECT nationname, COUNT(*) AS midfield_count,
           RANK() OVER(ORDER BY COUNT(*) DESC) AS rnk
    FROM Player
    WHERE Role = 'midfield'
    GROUP BY nationname
)
WHERE rnk = 1;

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