1811 寻找面试候选人

SQL架构

Create table If Not Exists Contests_1811 (contest_id int, gold_medal int, silver_medal int, bronze_medal int);
Create table If Not Exists Users_1811 (user_id int, mail varchar(50), name varchar(30));
Truncate table Contests_1811;
insert into Contests_1811 (contest_id, gold_medal, silver_medal, bronze_medal) values ('190', '1', '5', '2');
insert into Contests_1811 (contest_id, gold_medal, silver_medal, bronze_medal) values ('191', '2', '3', '5');
insert into Contests_1811 (contest_id, gold_medal, silver_medal, bronze_medal) values ('192', '5', '2', '3');
insert into Contests_1811 (contest_id, gold_medal, silver_medal, bronze_medal) values ('193', '1', '3', '5');
insert into Contests_1811 (contest_id, gold_medal, silver_medal, bronze_medal) values ('194', '4', '5', '2');
insert into Contests_1811 (contest_id, gold_medal, silver_medal, bronze_medal) values ('195', '4', '2', '1');
insert into Contests_1811 (contest_id, gold_medal, silver_medal, bronze_medal) values ('196', '1', '5', '2');
Truncate table Users_1811;
insert into Users_1811 (user_id, mail, name) values ('1', 'sarah@leetcode.com', 'Sarah');
insert into Users_1811 (user_id, mail, name) values ('2', 'bob@leetcode.com', 'Bob');
insert into Users_1811 (user_id, mail, name) values ('3', 'alice@leetcode.com', 'Alice');
insert into Users_1811 (user_id, mail, name) values ('4', 'hercy@leetcode.com', 'Hercy');
insert into Users_1811 (user_id, mail, name) values ('5', 'quarz@leetcode.com', 'Quarz');

表: Contests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| contest_id   | int  |
| gold_medal   | int  |
| silver_medal | int  |
| bronze_medal | int  |
+--------------+------+
contest_id 是该表的主键.
该表包含LeetCode竞赛的ID和该场比赛中金牌、银牌、铜牌的用户id。
可以保证，所有连续的比赛都有连续的ID，没有ID被跳过。

Table: Users

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| mail        | varchar |
| name        | varchar |
+-------------+---------+
user_id 是该表的主键.
该表包含用户信息。
 

编写 SQL 语句来返回面试候选人的 姓名和 邮件.当用户满足以下两个要求中的任意一条，其成为面试候选人:

该用户在连续三场及更多比赛中赢得奖牌。
该用户在三场及更多不同的比赛中赢得金牌（这些比赛可以不是连续的）
可以以任何顺序返回结果。

查询结果格式如下例所示：

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), 所以我们将他列入结果表。

解题

select B.name, b.mailfrom (select a.gold_medalfrom Contests_1811 agroup by a.gold_medalhaving count(1) >= 3UNIONselect A.Gold_Medalfrom (select a.*,a.contest_id - row_number() over(partition by a.gold_medal order by a.contest_id) rnfrom (select a.contest_id, a.gold_medalfrom Contests_1811 aunion allselect a.contest_id, a.silver_medalfrom Contests_1811 aunion allselect a.contest_id, a.bronze_medal from Contests_1811 a) A) AGROUP BY A.rn, A.Gold_MedalHAVING COUNT(1) >= 3) A,Users_1811 BWHERE A.Gold_Medal = B.User_Id;