SQL，力扣题目1194，锦标赛优胜者

devtools/2024/11/14 6:17:48/

一、力扣链接

LeetCode1194

二、题目描述

Players 玩家表

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id 是此表的主键(具有唯一值的列)。
此表的每一行表示每个玩家的组。

Matches 赛事表

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id 是此表的主键(具有唯一值的列)。
每一行是一场比赛的记录，first_player 和 second_player 表示该场比赛的球员 ID。
first_score 和 second_score 分别表示 first_player 和 second_player 的得分。
你可以假设，在每一场比赛中，球员都属于同一组。

每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小的选手获胜。

编写解决方案来查找每组中的获胜者。

返回的结果表单 没有顺序要求 。

三、目标拆解

四、建表语句

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')

五、过程分析

1、纵向拼接所有选手id的分数

2、分组并排序

3、筛选rn为1的

六、代码实现

with t1 as(
select first_player player_id, first_score score from Matches
union all 
select second_player, second_score from Matches
)
,t2 as(select player_id, (select group_id from Players p where p.player_id = t1.player_id) group_id,row_number() over(partition by (select group_id from Players p where p.player_id = t1.player_id) order by sum(score) desc) rnfrom t1group by group_id, player_id
)
select group_id, player_id
from t2 where rn = 1;