❤️博客主页: 楚生辉
❤️系列专栏:【LeetCode刷题】
❤️一句短话: 坚持不懈,孜孜不倦
1.题目描述
表: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 。
结果集 无顺序要求 。
查询结果的格式,如下所示。
输入:
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) 没有赢得,因此不包含在结果集中。
2.代码实现
- 方法一:列转行,相关行列转换的知识可以看一下SQL四种方法实现行列转换超详细这一篇文章
SELECT
p.player_id,
p.player_name,
sum( player_id = wimbledon )+ sum( player_id = fr_open )+ sum( player_id = us_open )+ sum( player_id = au_open ) AS grand_slams_count
FROM
players p
JOIN championships c
GROUP BY
p.player_id,
p.player_name
HAVING
grand_slams_count > 0
- 方法二:行转列,我们发现
Championships
表后面的所有数据都在同一列,那么这道题目的难度就会变得大大的简单
select player_id,player_name, count(*) grand_slams_count from
players join
(select wimbledon from championships
union all
select fr_open from championships
union all
select us_open from championships
union all
select au_open from championships)t
on player_id=wimbledon
group by player_id,player_name