列表

详情


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 。

结果集 无顺序要求

结果的格式,如下所示。

 

示例 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)  没有赢得,因此不包含在结果集中。

原站题解

去查看

上次编辑到这里,代码来自缓存 点击恢复默认模板
# Write your MySQL query statement below

mysql 解法, 执行用时: 822 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 19:47:33

with t as(
    -- 用union all聚合时,在第一行对字段重命名,方便后续用using()
    select Wimbledon player_id from Championships
    union all
    select Fr_open  from Championships
    union all
    select US_open  from Championships
    union all
    select Au_open  from Championships
)
select
    player_id, player_name, count(1) grand_slams_count
from
    t join Players p using(player_id)
group by
    1

mysql 解法, 执行用时: 1116 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 19:47:16

# Write your MySQL query statement below
select 
    p.player_id,p.player_name,count(*) as grand_slams_count 
from
    players as p inner 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) as t
on 
    p.player_id = t.wimbledon
group by 
    p.player_id

上一题