LeetCode MySQL 1194. 锦标赛优胜者
LeetCode MySQL 1194. 锦标赛优胜者
Michael阿明
发布于 2021-02-19 10:42:22
发布于 2021-02-19 10:42:22
文章被收录于专栏：Michael阿明学习之路
文章目录
1. 题目
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 |
+-----------+------------+来源：力扣（LeetCode） 链接：https://leetcode-cn.com/problems/tournament-winners 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
2. 解题
- 先把分数加起来
select player_id, sum(score) total_score
from
(
(
select first_player player_id, first_score score
from Matches
)
union all
(
select second_player, second_score
from Matches
)
) t1
group by player_id{"headers": ["player_id", "total_score"],
"values": [[15, 3], [30, 3], [40, 5], [35, 1],
[45, 0], [25, 2], [20, 2], [50, 1]]}- 左连接获取 分组id
- 窗口函数获取 组内排名
select group_id, player_id,
rank() over(partition by group_id order by total_score desc, player_id) rnk
from
(
select group_id, t2.player_id, total_score
from Players p left join
(
select player_id, sum(score) total_score
from
(
(
select first_player player_id, first_score score
from Matches
)
union all
(
select second_player, second_score
from Matches
)
) t1
group by player_id
) t2
using(player_id)
) t3- 取出排名为1的
# Write your MySQL query statement below
select group_id, player_id
from
(
select group_id, player_id,
rank() over(partition by group_id order by total_score desc, player_id) rnk
from
(
select group_id, t2.player_id, total_score
from Players p left join
(
select player_id, sum(score) total_score
from
(
(
select first_player player_id, first_score score
from Matches
)
union all
(
select second_player, second_score
from Matches
)
) t1
group by player_id
) t2
using(player_id)
) t3
) t
where rnk = 1本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020/08/01 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
