SQL架构
表: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) 没有赢得,因此不包含在结果集中。
union all:
select
ss1.player_id,p.player_name, ss1.grand_slams_count
from
(
select
player_id,count(player_id) grand_slams_count
from
(select
`year`,wimbledon player_id
from
Championships
union all
select
`year`,Fr_open player_id
from
Championships
union all
select
`year`,Au_open player_id
from
Championships
union all
select
`year`,US_open player_id
from
Championships) s1
group by player_id
) ss1 left join Players p using(player_id)
返回
join 和 if:
select
p.player_id,p.player_name,sum(if(c.Wimbledon = p.player_id,1,0) + if(c.Fr_open = p.player_id,1,0)+ if(c.US_open = p.player_id,1,0)+ if(c.Au_open = p.player_id,1,0)) grand_slams_count
from
Championships c join Players p
group by player_id
having grand_slams_count>0