# Write your MySQL query statement below
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) 没有赢得,因此不包含在结果集中。
原站题解
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