如何按马克斯分组？

Question

我在声明中遇到了一些麻烦， I have a table like this

Player Number	Sport	Date
1	soccer	4/1
4	basketball	4/2
2	basketball	4/3
3	soccer	4/1
1	baseball	4/2
4	basketball	4/3
5	soccer	4/1
3	baseball	4/2
5	soccer	4/2
2	basketball	4/3
1	soccer	4/4
2	baseball	4/5
3	soccer	4/4
4	soccer	4/5
5	soccer	4/3
5	basketball	4/5

I'm trying to write a query that will give me a table that will group together the players by their most played sport so the end result will look like this:

Sport	# of Players Who played the sport the most
Baseball	2
Basketball	1
Soccer	2

I was able to write a statement that shows the每个球员都参加AA特定运动的次数，但是很难找到桌子，只能选择比赛数量最多的行，并安排每种运动将其分组。

Select distinct PLAYER_NUMBER, SPORT, Max(GAMES_PLAYED) as GAMES_PLAYED
FROM #temp
Group By SPORT, PLAYER_NUMER
order by GAMES_PLAYED desc 

Answer 1

我认为您想要的结果与您的示例数据不匹配？

无论如何，从查询开始获取每个玩家的比赛次数，然后使用 row_number 窗口函数来确定每个玩家最常玩的运动项目（请注意在以下情况下您想要做什么领带？）。然后在下一个查询中仅使用每个运动员和按运动分组最多的运动。

with cte as (
    select *
        -- For each player get the sport with the most plays
        , row_number() over (partition by Player order by count(*) desc) rn
    from #temp
    group by Player, Sport
)
select Sport
    , count(*) [# of Players Who played the sport the most]
from cte
where rn = 1
group by Sport
order by Sport;

返回： 参加

体育运动	该运动最多的运动员的
编号篮球	2
足球	3

以下相关查询允许确认上面显示的结果：

with cte as (
    select *
        , count(*) num
        , row_number() over (partition by Player order by count(*) desc) rn
    from #temp
    group by Player, Sport
)
select Player, Sport, num
from cte
order by Player, num desc;

其中显示每个运动员参加最多的运动，即每个运动员的第一行，以及请注意，棒球从来没有成为最受欢迎的运动！实际结果是使用每个 rn=1 计算的，因为这是每个玩家参与最多的运动。

球员	运动	编号	rn
1	足球	2	1
1	棒球	1	2
2	篮球	2	1
2	棒球	1	2
3	足球	2	1
3	棒球	1	2
4	篮球	2	1
4	足球	1	2
5	足球	3	1
5	篮球	1	2

Answer 2

理解您，

select s.sport,
       count(distinct s.playdate) as times_played,
       (select max(t.players) from (
          select count(distinct s2.player) as players
          from   sports s2 
          where  s2.sport = s.sport 
          group by s2.sport, s2.playdate
          ) t
       ) as nbr_players
from   sports s
group by s.sport

这

。	来	一点
如果	可能	Subquies
我	也	会
正确	获得	通过

您 rel =“ nofollow noreferrer”> dbfiddle 在这里