1194.锦标赛优胜者

• 题目：

• 

• sql建表语句：

• Create table If Not Exists Players (player_id int, group_id int)
  Create table If Not Exists Matches (match_id int, first_player int, second_player int, first_score int, second_score int)
  
  Truncate table Players
  insert into Players (player_id, group_id) values ('10', '2')
  insert into Players (player_id, group_id) values ('15', '1')
  insert into Players (player_id, group_id) values ('20', '3')
  insert into Players (player_id, group_id) values ('25', '1')
  insert into Players (player_id, group_id) values ('30', '1')
  insert into Players (player_id, group_id) values ('35', '2')
  insert into Players (player_id, group_id) values ('40', '3')
  insert into Players (player_id, group_id) values ('45', '1')
  insert into Players (player_id, group_id) values ('50', '2')
  Truncate table Matches
  insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('1', '15', '45', '3', '0')
  insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('2', '30', '25', '1', '2')
  insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('3', '30', '15', '2', '0')
  insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('4', '40', '20', '5', '2')
  insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('5', '35', '50', '1', '1')

• 分析：当看到题目之后，我开始想的是分两次连接，分别连接比赛表中的第一名和第二名，然后计算每个人的分数，但是我感觉那样写很麻烦，然后我就又想了一个办法，就是把第一名和第二名的分数全取出，然后拼接到一起，之后在和运动员表连接，算出每个人的分数，然后筛选出每组最高分。下面是图表分析：

• 

• sql实现：

• with t1 as (select first_player, first_score
              from Matches
              union all
              select second_player, second_score
              from Matches),         -- 拼接所有的获得名次的运动员
      t2 as (
          select group_id,player_id,sum(first_score) score from Players p1,t1 where p1.player_id=t1.first_player group by player_id,group_id -- 连接运动员表，找出每个运动员得分
      ),
      t3 as (
          select player_id,group_id,row_number() over (partition by group_id order by score desc ,player_id) rn from t2           -- 用窗口函数把每组的分数排序
      )
  select group_id,player_id from t3 where rn=1; -- 取出每组分数最高的运动员id