锦标赛优胜者****
难度困难
SQL架构
Players 玩家表
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| player_id | int |
| group_id | int |
+-------------+-------+
玩家 ID 是此表的主键。
此表的每一行表示每个玩家的组。
Matches 赛事表
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| match_id | int |
| first_player | int |
| second_player | int |
| first_score | int |
| second_score | int |
+---------------+---------+
match_id 是此表的主键。
每一行是一场比赛的记录，第一名和第二名球员包含每场比赛的球员 ID。
第一个玩家和第二个玩家的分数分别包含第一个玩家和第二个玩家的分数。
你可以假设，在每一场比赛中，球员都属于同一组。
每组的获胜者是在组内得分最高的选手。如果平局，player_id 最小 的选手获胜。
编写一个 SQL 查询来查找每组中的获胜者。
查询结果格式如下所示
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 |
+------------+--------------+---------------+-------------+--------------+
Result 表:
+-----------+------------+
| group_id | player_id |
+-----------+------------+
| 1 | 15 |
| 2 | 35 |
| 3 | 40 |
+-----------+------------+
方法1：
select p.group_id, p.player as play_id from
(
select b.group_id, t.player, row_number() over(partition by b.group_id order by t.ts desc, t.player asc)as r from
(
select a.player, sum(a.score) as ts from
(
select first_player as player, first_score as score from matches
union all
select second_player as player, second_score as score from matches
) as a
group by a.player
) as t
inner join players b
on t.player = b.player_id
) as p
where p.r = 1;
方法2：UNION ALL
思路
本题要求 每组的获胜者是在组内得分最高的选手，那么肯定要求出每个选手的总得分。每个选手的得分可能是 first_score 或者 second_score，我们需要将两个分数相加。
我们可以使用 GROUP BY 和 SUM() 函数，求出每个用户的 first_score 的和。
Mysql
SELECT player_id, SUM(first_score) AS score
FROM Matches
GROUP BY player_id
因为需要知道每个选手的 group_id，可以使用 JOIN 将 Players 表 和 Matches 表通过 player_id 关联来获取 group_id。
Mysql
SELECT Players.group_id, Players.player_id, SUM(Matches.first_score) AS score
FROM Players JOIN Matches ON Players.player_id = Matches.first_player
GROUP BY Players.player_id
同样的方法我们可以计算出每个用户总的 second_score。
得到每个选手两个分数的总和后将两个分数相加，得到每个用户的总得分。我们可以使用 UNION ALL 将这两个结果集合并。将这些数据看作数据表，使用 FROM 子句和 GROUP BY。
Mysql
SELECT group_id, player_id, SUM(score) AS score
FROM (
SELECT Players.group_id, Players.player_id, SUM(Matches.first_score) AS score
FROM Players JOIN Matches ON Players.player_id = Matches.first_player
GROUP BY Players.player_id
UNION ALL
SELECT Players.group_id, Players.player_id, SUM(Matches.second_score) AS score
FROM Players JOIN Matches ON Players.player_id = Matches.second_player
GROUP BY Players.player_id
) s
GROUP BY player_id
得到每个用户的总分后，需要找到组内得分最高的选手。那么我们可以使用 ORDER BY，先根据得分倒序排列。如果平局，player_id 最小的选手获胜，那么只要再根据 player_id 正序排。
Mysql
ORDER BY score DESC, player_id
最后只需要再使用一次 FROM 子句和 GROUP BY 取出每个组的第一条数据即可。
代码
Mysql
SELECT group_id, player_id
FROM (
SELECT group_id, player_id, SUM(score) AS score
FROM (
-- 每个用户总的 first_score
SELECT Players.group_id, Players.player_id, SUM(Matches.first_score) AS score
FROM Players JOIN Matches ON Players.player_id = Matches.first_player
GROUP BY Players.player_id
UNION ALL
-- 每个用户总的 second_score
SELECT Players.group_id, Players.player_id, SUM(Matches.second_score) AS score
FROM Players JOIN Matches ON Players.player_id = Matches.second_player
GROUP BY Players.player_id
) s
GROUP BY player_id
ORDER BY score DESC, player_id
) result
GROUP BY group_id
方法3：
select group_id,player_id from
(select group_id,player_id,sum((
case when player_id = first_player then first_score
when player_id = second_player then second_score
end
)) as totalScores
from Players p,Matches m
where p.player_id = m.first_player
or p.player_id = m.second_player
group by group_id,player_id
order by group_id,totalScores desc,player_id) as temp
group by group_id
order by group_id,totalScores desc,player_id
