题目

题目链接
表：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)  没有赢得，因此不包含在结果集中。

解题

方法一：

1.将Championships表行转成列
2.统计每个id的人冠军次数
3.用内联结，获得结果

select 
    p.player_id,
    p.player_name,
    c.grand_slams_count
from Players as p
inner join(
    select id,count(*) as grand_slams_count
    from (
        select Wimbledon as id
        from Championships
        union all
        select Fr_open as id
        from Championships
        union all
        select US_open as id
        from Championships
        union all
        select Au_open as id
        from Championships
    )as b
    group by id
)as c
on p.player_id=c.id;

方法二：

innner join不加，连结on关键字，就是求笛卡尔积
这种效率相比方法一会低一点

select
    p.player_id,
    p.player_name,
    sum(
        (case when c.Wimbledon=p.player_id then 1 else 0 end)+
        (case when c.Fr_open=p.player_id then 1 else 0 end)+
        (case when c.US_open=p.player_id then 1 else 0 end)+
        (case when c.Au_open=p.player_id then 1 else 0 end)
    )as grand_slams_count
from Championships as c
inner join Players as p
group by p.player_id
having grand_slams_count<>0;