1783. 大满贯数量

1844247

已于 2024-12-13 09:56:40 修改

阅读量687

点赞数 29

分类专栏： # 中等plus leedcode 数据库题目解析文章标签：数据库 leetcode 算法 sql mysql

于 2024-10-26 14:58:31 首次发布

本文链接：https://blog.csdn.net/CYJ1844/article/details/143253491

版权

leedcode 数据库题目解析同时被 2 个专栏收录

180 篇文章

订阅专栏

中等plus

102 篇文章

订阅专栏

力扣题目跳转（1783. 大满贯数量 - 力扣（LeetCode））

表：Players

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| player_id      | int     |
| player_name    | varchar |
+----------------+---------+
player_id 是这个表的主键（具有唯一值的列）
这个表的每一行给出一个网球运动员的 ID 和 姓名

表：Championships

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| year          | int     |
| Wimbledon     | int     |
| Fr_open       | int     |
| US_open       | int     |
| Au_open       | int     |
+---------------+---------+
year 是这个表的主键（具有唯一值的列）
该表的每一行都包含在每场大满贯网球比赛中赢得比赛的球员的 ID

题目要求：

编写解决方案，找出每一个球员赢得大满贯比赛的次数。结果不包含没有赢得比赛的球员的ID 。

结果集 无顺序要求 。

结果的格式，如下所示。

示例 1:

输入：
Players 表：
+-----------+-------------+
| player_id | player_name |
+-----------+-------------+
| 1         | Nadal       |
| 2         | Federer     |
| 3         | Novak       |
+-----------+-------------+
Championships 表：
+------+-----------+---------+---------+---------+
| year | Wimbledon | Fr_open | US_open | Au_open |
+------+-----------+---------+---------+---------+
| 2018 | 1         | 1       | 1       | 1       |
| 2019 | 1         | 1       | 2       | 2       |
| 2020 | 2         | 1       | 2       | 2       |
+------+-----------+---------+---------+---------+
输出：
+-----------+-------------+-------------------+
| player_id | player_name | grand_slams_count |
+-----------+-------------+-------------------+
| 2         | Federer     | 5                 |
| 1         | Nadal       | 7                 |
+-----------+-------------+-------------------+
解释：
Player 1 (Nadal) 获得了 7 次大满贯：其中温网 2 次(2018, 2019), 法国公开赛 3 次 (2018, 2019, 2020), 美国公开赛 1 次 (2018)以及澳网公开赛 1 次 (2018) 。
Player 2 (Federer) 获得了 5 次大满贯：其中温网 1 次 (2020), 美国公开赛 2 次 (2019, 2020) 以及澳网公开赛 2 次 (2019, 2020) 。
Player 3 (Novak)  没有赢得，因此不包含在结果集中。

case 1 的建表语句。

Create table If Not Exists Players (player_id int, player_name varchar(20))
Create table If Not Exists Championships (year int, Wimbledon int, Fr_open int, US_open int, Au_open int)
Truncate table Players
insert into Players (player_id, player_name) values ('1', 'Nadal')
insert into Players (player_id, player_name) values ('2', 'Federer')
insert into Players (player_id, player_name) values ('3', 'Novak')
Truncate table Championships
insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2018', '1', '1', '1', '1')
insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2019', '1', '1', '2', '2')
insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2020', '2', '1', '2', '2')

一使用 union all 把每列的数据放在一列

select
    Wimbledon as player_id
from Championships
union all
select
    Fr_open player_id
from Championships
union all
select
    US_open as player_id
from Championships
union all
select
    Au_open as player_id
from Championships

输出如下

二然后和 players 进行连接，选取需要的字段，进行分组。

with tmp as
(select
    Wimbledon as player_id
from Championships
union all
select
    Fr_open player_id
from Championships
union all
select
    US_open as player_id
from Championships
union all
select
    Au_open as player_id
from Championships)
select
    p.player_id,
    player_name,
    count(*) as grand_slams_count
from tmp t
join Players p on t.player_id = p.player_id
group by p.player_id, player_name