一、题目来源
1811. 寻找面试候选人 - 力扣(LeetCode)
二、数据表结构
表:
Contests
+--------------+------+ | Column Name | Type | +--------------+------+ | contest_id | int | | gold_medal | int | | silver_medal | int | | bronze_medal | int | +--------------+------+ contest_id 是该表的主键. 该表包含LeetCode竞赛的ID和该场比赛中金牌、银牌、铜牌的用户id。 可以保证,所有连续的比赛都有连续的ID,没有ID被跳过。表:
Users
+-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | mail | varchar | | name | varchar | +-------------+---------+ user_id 是该表的主键. 该表包含用户信息。
三、需求
编写 SQL 语句来返回 所有面试候选人 的姓名 name
和邮件 mail
。当用户满足以下两个要求中的 任意一条 ,其成为 面试候选人 :
- 该用户在 连续三场及更多 比赛中赢得 任意 奖牌。
- 该用户在 三场及更多不同的 比赛中赢得 金牌(这些比赛可以不是连续的)
可以以 任何顺序 返回结果。
四、示例数据
输入: Contests表: +------------+------------+--------------+--------------+ | contest_id | gold_medal | silver_medal | bronze_medal | +------------+------------+--------------+--------------+ | 190 | 1 | 5 | 2 | | 191 | 2 | 3 | 5 | | 192 | 5 | 2 | 3 | | 193 | 1 | 3 | 5 | | 194 | 4 | 5 | 2 | | 195 | 4 | 2 | 1 | | 196 | 1 | 5 | 2 | +------------+------------+--------------+--------------+ Users表: +---------+--------------------+-------+ | user_id | mail | name | +---------+--------------------+-------+ | 1 | sarah@leetcode.com | Sarah | | 2 | bob@leetcode.com | Bob | | 3 | alice@leetcode.com | Alice | | 4 | hercy@leetcode.com | Hercy | | 5 | quarz@leetcode.com | Quarz | +---------+--------------------+-------+ 输出: +-------+--------------------+ | name | mail | +-------+--------------------+ | Sarah | sarah@leetcode.com | | Bob | bob@leetcode.com | | Alice | alice@leetcode.com | | Quarz | quarz@leetcode.com | +-------+--------------------+ 解释: Sarah 赢得了3块金牌 (190, 193, and 196),所以我们将她列入结果表。 Bob在连续3场竞赛中赢得了奖牌(190, 191, and 192), 所以我们将他列入结果表。- 注意他在另外的连续3场竞赛中也赢得了奖牌(194, 195, and 196). Alice在连续3场竞赛中赢得了奖牌 (191, 192, and 193), 所以我们将她列入结果表。 Quarz在连续5场竞赛中赢得了奖牌(190, 191, 192, 193, and 194), 所以我们将他列入结果表。
五、分析
1.文字分析
本题需求为寻找面试候选人:
第一步:首先寻找三场及更多获得金牌的人,将两表关联,关联条件为u.user_id = c.gold_medal,然后使用count()函数获取用户获取金牌次数,取次数大于等于3的用户;
第二步:获取连续三场及更多获取奖牌的用户:两表关联,关联条件为user_id in (gold_medal,silver_medal,bronze_medal),然后使用row_Number()函数进行排名user_id分组,contest_Id 排序获得排名,使用contest_id减去排名,得到diff值,如果连续场次获得奖牌,diff是相同的,根据name,mail,diff进行分组,使用count()函数进行统计,获取count() 大于等于 3 的结果;
第三步:使用union将前两步结果进行联合输出,得到最终结果。
2.图解
六、代码实现
SELECTu.name,u.mail from Contests c join users u on c.gold_medal = u.user_id GROUP BY u.name, u.mail HAVING count(*) >= 3 union (with t1 AS (SELECTu.name,u.mail,contest_id - row_number() OVER (partition by user_id ORDER BY contest_id) as difffrom users ujoin contests c on user_id in (gold_medal,silver_medal,bronze_medal))SELECTname,mail from t1 GROUP BY name, mail,diff HAVING count(*) >= 3);
七、总结
本题需求为寻找面试候选人,难点在于求连续三场及更多获得奖牌的用户:
求连续问题的通用思路为首先观察表中数据是否存在等差数列,如果存在等差数列,那只需要再创造一个等差数列即可,通常使用row_number() 排名函数获取等差数列,然后观察数据 ,求两个等差数列的差值,通过差值及其他字段进行分组,差值相同的即为连续的,通过差值及其他字段进行分组,使用count()函数统计差值相同的次数,过滤出满足题目需求的值即可。