mysql - 按多列分组后选择最大计数

Question

我有一个包含架构和数据的表，如下所示

id,winner,season  
1, Chelsea, 2014 
2, Chelsea, 2014 
3, Chelsea, 2015 
4, Arsenal, 2014

。.

等等。

基本上，每个赛季的每场比赛都有一个条目。

我需要查询并确定每个赛季赢得比赛次数最多的球队。

我直到检索到每个赛季的最大比赛场数，但无法检索到最多比赛场次的球队的名称。有线索吗？

with  t1 as
(select count(winner) as cnt, winner, season from matches group by winner,season)
select max(cnt),season from t1 group by season

Answer 1

这个应该可以工作:

select t2.*
from (
select season, max(totalwins) as totalwins from (
select season, winner, count(*) as totalwins
  from matches t
  group by season, winner
) s1 group by season) w join
(select season, winner, count(*) as totalwins
  from matches t
  group by season, winner
) t2 on t2.season = w.season and t2.totalwins = w.totalwins;

结果:

season  winner  totalwins
------  ------- ---------
2014    Chelsea 2
2015    Arsenal 1
2015    Chelsea 1