力扣-Mysql-1811 - 寻找面试候选人（中等）
一、题目来源
二、数据表结构
表:
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.图解
六、代码实现
SELECT u.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 ( SELECT u.name, u.mail, contest_id - row_number() OVER (partition by user_id ORDER BY contest_id) as diff from users u join contests c on user_id in (gold_medal,silver_medal,bronze_medal) ) SELECT name, mail from t1 GROUP BY name, mail,diff HAVING count(*) >= 3 );
七、总结
本题需求为寻找面试候选人，难点在于求连续三场及更多获得奖牌的用户：
求连续问题的通用思路为首先观察表中数据是否存在等差数列，如果存在等差数列，那只需要再创造一个等差数列即可，通常使用row_number() 排名函数获取等差数列，然后观察数据 ，求两个等差数列的差值，通过差值及其他字段进行分组，差值相同的即为连续的，通过差值及其他字段进行分组，使用count()函数统计差值相同的次数，过滤出满足题目需求的值即可。
原文地址：https://blog.csdn.net/Jotcy/article/details/143887577
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！