如何使用SQL从每个组中获得最大得分玩家?

问题描述

我有桌上的球员和比赛，我想寻找最高分的球员，请注意，如果得分匹配，则ID较低的球员在每个组中都是赢家.

I have tables players and matches and I want to find players with max points, note that player with lower id is winner in each group if scores matches.

create table players (
      player_id integer not null unique,
      group_id integer not null
  );

  create table matches (
      match_id integer not null unique,
      first_player integer not null,
      second_player integer not null,
      first_score integer not null,
      second_score integer not null
  );



insert into players values(20, 2);
insert into players values(30, 1);
insert into players values(40, 3);
insert into players values(45, 1);
insert into players values(50, 2);
insert into players values(65, 1);
insert into matches values(1, 30, 45, 10, 12);
insert into matches values(2, 20, 50, 5, 5);
insert into matches values(13, 65, 45, 10, 10);
insert into matches values(5, 30, 65, 3, 15);
insert into matches values(42, 45, 65, 8, 4);

现在我想要结果

请注意，第一名和第二名玩家可以在组中相同.

Note that first and second player can be same from group.

结果:-

group_id | winner_id
  ----------+-----------
   1        | 45
   2        | 20
   3        | 40

我不确定如何进行.

推荐答案

使用row_number():

select group_id, player_id
from (
    select
        p.*,
        row_number() over(
            partition by p.group_id 
            order by case 
                when m.first_player = p.player_id then m.first_score 
                else m.second_score 
            end desc,
            player_id
        ) rn
    from players p
    inner join matches m
        on m.first_player = p.player_id or m.second_player = p.player_id
) x
where rn = 1

DB Fiddle上的演示 :

Demo on DB Fiddle:

| group_id | player_id |
| -------- | --------- |
| 1        | 65        |
| 2        | 20        |

注意:第3组中只有一名玩家(player_id 40)，他们没有参加任何游戏.

Note: there is just one player in group 3 (player_id 40), and they didn't participate any game.

这篇关于如何使用SQL从每个组中获得最大得分玩家?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！