SQL，力扣题目1783，大满贯数量

一、力扣链接

1783. 大满贯数量

二、题目描述

表：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 。

结果集 无顺序要求 。

三、目标拆解

四、建表语句

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')

五、过程分析

1、计算所有字段不同player_id的个数，选择union all 方法

2、根据新表进行分组聚合，子查询查找球员姓名

六、代码实现

with t1 as(
select year, Wimbledon player_id
from Championships
union all
select year, Fr_open
from Championships
union all 
select year, US_open
from Championships
union all
select year, Au_open
from Championships
)
select player_id,
       (select player_name from players p where p.player_id = t1.player_id) player_name,
       count(player_id) grand_slams_count
from t1 group by player_id;

七、结果验证

八、小结

1、union all 纵向拼接所有字段内容，用于后续整体对表格进行操作

2、select 后的子查询通过where 条件进行连接，返回单个值

3、聚合函数count() 用于计算个数