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

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

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)

DEMO

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;