每日一题-37（寻找面试候选人）

题37：

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

• 该用户在连续三场及更多比赛中赢得奖牌；

• 该用户在三场及更多不同的比赛中赢得金牌（这些比赛可以不是连续的）
  
  其中：

• Contests表中contest_id 是主键。该表包含LeetCode竞赛的ID和该场比赛中金牌、银牌、铜牌的用户id。所有连续的比赛都有连续的ID，没有ID被跳过；

• Users表中user_id 是主键。该表包含用户信息。

解题思路：
（1）首先建立一个看每人每年是否得奖的表格

with t as  ( 
    SELECT Users.user_id as user_id, 
           Contests.contest_id as contest_id, 
           1 as got_medal
    FROM Users, Contests
    WHERE gold_medal = Users.user_id
     OR silver_medal = Users.user_id
     OR bronze_medal = Users.user_id
    ORDER BY Users.user_id, Contests.contest_id )

（2）查询得过三次金奖的人

SELECT gold_medal
FROM Contests
GROUP BY gold_medal
HAVING COUNT(*) >= 3

（3）查询连续三年得奖的人

SELECT DISTINCT a.user_id
FROM t a, t b, t c
WHERE a.user_id = b.user_id
  AND b.user_id = c.user_id
  AND a.contest_id + 1 = b.contest_id
  AND b.contest_id + 1 = c.contest_id 

（4）连接（2）和（3）然后作为放在主查询where里面即可。

with t as  ( 
    SELECT Users.user_id as user_id, 
           Contests.contest_id as contest_id, 
           1 as got_medal
    FROM Users, Contests
    WHERE gold_medal = Users.user_id
     OR silver_medal = Users.user_id
     OR bronze_medal = Users.user_id
    ORDER BY Users.user_id, Contests.contest_id )

SELECT name, mail
FROM Users
WHERE user_id IN (
    SELECT gold_medal
    FROM Contests
    GROUP BY gold_medal
    HAVING COUNT(*) >= 3
        UNION
    SELECT DISTINCT a.user_id
    FROM t a, t b, t c
    WHERE a.user_id = b.user_id
      AND b.user_id = c.user_id
      AND a.contest_id + 1 = b.contest_id
      AND b.contest_id + 1 = c.contest_id  )
;