Oracle:组合子查询和聚合函数

Question

我对数据库和甲骨文都很陌生，我应该在一张摆满世界杯所有球队的桌子上找到，这些国家的中场球员都是最多的。这是表格的结构：

Player(PlayerID<PK>,Role,Nationname)

我承认，我并不真正了解子查询如何与聚合函数协同工作。我试图像这样在表中找到最大的中场数量，但是我没有得到任何输出：

SELECT DISTINCT nationname, count(role)
FROM player
GROUP BY nationname
HAVING COUNT(role) = (SELECT max(count(role))
                   FROM player
                   WHERE role = 'midfield'
                   GROUP BY nationname);

我还尝试使用FROM子句中的子查询(我在这个站点上看到许多人使用这种子查询)如下：

SELECT nationname, MAX(midfield) FROM 
( SELECT nationname, COUNT(role) midfield
  FROM player
  WHERE role = 'midfield'
  GROUP BY nationname) test
  GROUP BY nationname;

输出：

​

有了这个查询，我越来越接近任务，但我不能进一步过滤结果，以便只得到洪都拉斯队。

我能为它做些什么？我真的很感激任何提示或帮助！

Answer 1

在内部查询中，选择并分组，然后在按计数的角色排序时可以选择第一行。

select *
from (SELECT nationname, COUNT(role) midfield
      FROM player
      WHERE role = 'midfield'
      GROUP BY nationname)
where rownum = 1
order by midfield desc

如果有多个国家在中场位置上拥有相同的最大球员数量，请使用以下方法：

SELECT nationname, COUNT(role) midfield
FROM player
WHERE role = 'midfield'
GROUP BY nationname
having count(role) in (select max(count(role)) 
                       from player 
                       where role = 'midfield' 
                       group by nationname)

演示

Answer 2

如果您使用rank解析函数(为什么不使用row_number )？因为两支(或更多)球队可以拥有相同数量的中场球员)，所以你可以。

with temp as
  -- rank them first, by count of midfield players in descending order
  (select nationname, 
          rank() over (order by count(*) desc) rnk, 
          count(*) cnt
   from player
   where role = 'midfield'
   group by nationname
  )
-- now, fetch nation which ranks as the highest
select nationname, cnt
from temp
where rnk = 1;