127，[行列转换+分组求TopN]SQL训练之，力扣，1194. 锦标赛优胜者

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

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 最小 的选手获胜。

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

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

返回结果格式如下所示。

示例 1:

输入：
Players 表:
+-----------+------------+
| player_id | group_id   |
+-----------+------------+
| 15        | 1          |
| 25        | 1          |
| 30        | 1          |
| 45        | 1          |
| 10        | 2          |
| 35        | 2          |
| 50        | 2          |
| 20        | 3          |
| 40        | 3          |
+-----------+------------+
Matches 表:
+------------+--------------+---------------+-------------+--------------+
| match_id   | first_player | second_player | first_score | second_score |
+------------+--------------+---------------+-------------+--------------+
| 1          | 15           | 45            | 3           | 0            |
| 2          | 30           | 25            | 1           | 2            |
| 3          | 30           | 15            | 2           | 0            |
| 4          | 40           | 20            | 5           | 2            |
| 5          | 35           | 50            | 1           | 1            |
+------------+--------------+---------------+-------------+--------------+
输出：
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+ 
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+

三，建表语句

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

四，分析

表格大法

第一步:拆matches表,取第一次比赛的id和成绩,第二次比赛的id和成绩

第二步:把它们纵向合并为一个表

第三步:用这个表内连接players表

第四步:以 group_id,playerid分组 sum 分数

第五步:以group_id 以分数降序排名 生成一个排序 允许并列

第六步:取并列第一 然后以group_id排序,以玩家id排名 生成一个排序

第七步,去第一,然后指定映射对应的id,并且输出

解题过程

代码实现上述思路

第一步:拆matches表,取第一次比赛的id和成绩,第二次比赛的id和成绩

在mysql

第二步:把它们纵向合并为一个表

在mysql

第三步:用这个表内连接players表

在mysql

第四步:以 group_id,playerid分组 sum 分数

在mysql

第五步:以group_id 以分数降序排名 生成一个排序 允许并列

在mysql

第六步:取并列第一 然后以group_id排序,以玩家id排名 生成一个排序

在mysql

第七步,去第一,然后指定映射对应的id,并且输出

在mysql

五，SQL解答

with t1 as (
select first_player as plaryer_id,first_score as score from matches
union all
select second_player,second_score from matches
)
#    select * from t1;
,t2 as (
    select
        plaryer_id, score,group_id
    from t1
        join players p on t1.plaryer_id=p.player_id
)
#    select * from t2;
   ,t3 as (
    select
        group_id,plaryer_id,sum(score) ss
    from t2  group by group_id, plaryer_id
)
#    select * from t3;
   ,t4 as (
    select
    group_id, plaryer_id, ss,
     rank() over (partition by group_id order by ss desc) rn
    from t3
)
#    select * from t4;
   ,t5 as (
    select group_id, plaryer_id, ss, rn,
       row_number() over (partition by group_id order by plaryer_id ) rn2
       from t4  where rn=1
)
# select * from t5;
select group_id as  group_id, plaryer_id as player_id  from t5 where rn2=1;

六，验证

七，知识点总结

• 行列转换
• union all 纵向合并的运用
• 分组聚合的运用
• 不同列组合分组求topN的运用
• rank排序 允许并列
• row_number开窗排序 不允许并列的运用

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用