目录
题目链接(无VIP请直接看下面的需求)
- 链接: 15分钟没思路建议直接看答案
题目和题目代码
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')
1.读题(建议使用这种表结构和数据对比看阅读)
______________________________________________
每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小 的选手获胜。
编写解决方案来查找每组中的获胜者。
2.答案代码以及图表解释
select *from players; select *from matches;
+---------+--------+ +--------+------------+-------------+-----------+------------+
|player_id|group_id| |match_id|first_player|second_player|first_score|second_score|
|球员_id(唯一)|组_id | |匹配_id(唯一)|第一_播放器 |第二_播放器 |第一_分 |第二_分|
+---------+--------+ +--------+------------+-------------+-----------+------------+
|15 |1 | |1 |15 |45 |3 |0 |
|25 |1 | |2 |30 |25 |1 |2 |
|30 |1 | |3 |30 |15 |2 |0 |
|45 |1 | |4 |40 |20 |5 |2 |
|10 |2 | |5 |35 |50 |1 |1 |
|35 |2 | +--------+------------+-------------+-----------+------------+
|50 |2 |每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小 的选手获胜。
|20 |3 | 每组 最高 平局 player_id 最小 关键字
|40 |3 |编写解决方案来查找每组中的获胜者。
+---------+--------+;
方法1;
with a1 as (
select first_player 球员_id,first_score 球员_分数 from Matches
union all
select second_player 球员_id,second_score 球员_分数 from Matches)
,a2 as (
select group_id,球员_id,sum(球员_分数) 总分,
row_number() over (partition by group_id order by sum(球员_分数) desc , 球员_id) rn
from a1
join Players p on p.player_id=a1.球员_id
group by group_id,球员_id)
select group_id,球员_id player_id from a2 where rn=1;
+-----+-----+
|球员_id|球员_分数|
+-----+-----+with a1 as (
|15 |3 |select first_player 球员_id,first_score 球员_分数 from Matches
|30 |1 |union all
|30 |2 |select second_player 球员_id,second_score 球员_分数 from Matches)
|40 |5 |先进行拆分原表,构建新表,在分组聚合,分组排序,取第一行即可
|35 |1 |,a2 as (
|45 |0 |select group_id,球员_id,sum(球员_分数) 总分,
|25 |2 | row_number() over (partition by group_id order by sum(球员_分数) desc , 球员_id) rn
|15 |0 |from a1
|20 |2 |join Players p on p.player_id=a1.球员_id
|50 |1 |group by group_id,球员_id)
+-----+-----+select group_id,球员_id player_id from a2 where rn=1;
+--------+-----+--+--+
|group_id|球员_id|总分|rn|
+--------+-----+--+--+
|1 |15 |3 |1 |
|1 |30 |3 |2 |
|1 |25 |2 |3 |
|1 |45 |0 |4 |
|2 |35 |1 |1 |
|2 |50 |1 |2 |
|3 |40 |5 |1 |
|3 |20 |2 |2 |
+--------+-----+--+--+
;
方法2;
with a1 as (
select
player_id, group_id,
# sum(if(player_id = first_player, first_score, second_score)) score,
row_number() over(partition by group_id order by
sum(if(player_id = first_player, first_score, second_score)) desc, player_id) rn
from players
join matches
on player_id = first_player or player_id = second_player
group by player_id, group_id)
select group_id,player_id from a1 where rn=1;
先进行join既要player_id = first_player又要player_id = second_player,
再对组_id,球员id分组,然后sum(if),这种方法确实有点意想不到,
重点了解,join时,通过on 条件 or 条件 ,能为sum(if)构造分字段sum的条件,
这种思路,还是值得学习的
+---------+--------+--------+------------+-------------+-----------+------------+
|player_id|group_id|match_id|first_player|second_player|first_score|second_score|
+---------+--------+--------+------------+-------------+-----------+------------+
|15 |1 |1 |15 |45 |3 |0 |
|15 |1 |3 |30 |15 |2 |0 |
sum(if(player_id = first_player, first_score, second_score))
|25 |1 |2 |30 |25 |1 |2 |
|30 |1 |2 |30 |25 |1 |2 |
|30 |1 |3 |30 |15 |2 |0 |
|45 |1 |1 |15 |45 |3 |0 |
|35 |2 |5 |35 |50 |1 |1 |
|50 |2 |5 |35 |50 |1 |1 |
|20 |3 |4 |40 |20 |5 |2 |
|40 |3 |4 |40 |20 |5 |2 |
+---------+--------+--------+------------+-------------+-----------+------------+
+---------+--------+-----+--+ +--------+---------+
|player_id|group_id|score|rn| |group_id|player_id|
+---------+--------+-----+--+ +--------+---------+
|15 |1 |3 |1 | |1 |15 |
|30 |1 |3 |2 | |2 |35 |
|25 |1 |2 |3 | |3 |40 |
|45 |1 |0 |4 | +--------+---------+
|35 |2 |1 |1 |
|50 |2 |1 |2 |
|40 |3 |5 |1 |
|20 |3 |2 |2 |
+---------+--------+-----+--+
