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

hiz5n14c 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(286)

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

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

sql mysql Database

来源：https://stackoverflow.com/questions/50456315/select-max-count-after-group-by-multiple-columns

1条答案

按热度按时间

lnlaulya1#

这个应该有用：

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

赞(0）回复(0）举报 2021-06-20

我来回答

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

1条答案

相关问题

热门标签

最新问答