力扣 SQL 刷题笔记

最近意识到 SQL 的重要性（Hive 要写 SQL，Spark 还要写 SQL，MapReduce 又可以用 SQL 来描述），并且大数据的各种平台作为分析型系统，复杂的查询是屡见不鲜的，因此打算对 SQL 进行专门学习，这里把 LeetCode 上所有免费 SQL 题目都刷一下，之后也要做做别的题目。尽量给出多于一个解决方案。

Easy

175. Combine Two Tables

有一张 Person 表和一张 Address 表，Person 表标识人的姓名，Address 表标识人的住址，它们的定义如下——

Person
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| personId    | int     |
| lastName    | varchar |
| firstName   | varchar |
+-------------+---------+

Address
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| addressId   | int     |
| personId    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+

现在，要获取某人的姓名对应的住址，注意有人可能没有住址，输入输出为——

Input: 
Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+
Output: 
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+

这道题目明显地过了头，可以看到，Wang Allen 这个人是没有住址的，因此对应字段为空，这是一个非常显然的外连接——

SELECT 
    firstName,
    lastName,
    city,
    state
FROM Person LEFT JOIN Address ON Person.personId = Address.personId;

开门红！

181. Employees Earning More Than Their Managers

现在有一张雇员表，其中 id 字段代表当前雇员的 id，managerId 代表它的上级的 id（也是雇员），现在要求查询工资比他的上级高的雇员的名称。

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+

一个最直接的想法是，对每个雇员，根据他的 managerId 找到他的上级的工资，然后用他的工资和上级的工资做比较，SQL 如下，这里使用了关联子查询——

SELECT e.name as Employee 
FROM Employee AS e 
WHERE salary > (SELECT salary FROM Employee AS m WHERE m.id = e.managerId);

但再看看这张表的结构，可以发现可以直接让自己和自己内连接，从而把自己的工资和上级的工资放到一张表中，从而能够进行比较。

SELECT e.name as Employee
FROM Employee as e JOIN Employee as m ON e.managerId = m.id
WHERE e.salary > m.salary;

输入输出为——

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+

顺带一提，这里使用左外连接的话，WHERE 执行前的结果的左边四个字段会多包含所有没有上级的雇员，即 Sam，Max，右边四个字段为 NULL；使用左外连接的话，WHERE 执行前的结果的右边四个字段会包含所有没有下级的雇员，即 Joe，Henry，左边四个为 NULL。

在进行这种思考时，把左右外连接看作内连接的结果 + 左/右边无法匹配的记录即可。

为表取一个合适的名字非常重要，比如左边的表叫雇员表，右边的表叫上级表，使用业务相关的术语来描述表效果非常好。

182. Duplicate Emails

考虑一个邮箱表，每个邮箱有自己的 id，id 为主键，邮箱名称可能重复，现在考虑获取有重复的邮箱的名称。

这显然是一个需要使用聚集操作的场合，考虑可以直接使用邮箱名进行分组，然后使用 HAVING，COUNT 统计出出现次数大于 1 的邮箱名称，代码非常简单。

SELECT email as Email FROM Person -- 这个表叫 Person？？
GROUP BY email
HAVING COUNT(*) > 1;

记住，HAVING 对分组后的数据进行筛选，因此可以使用聚集操作。

还有更 dummy 的解决方案——获取每个邮箱名称的出现次数，找到大于 1 的。

SELECT email FROM 
(SELECT email, COUNT(*) as num FROM Person GROUP BY email) as c
WHERE num > 1;

还有更弯弯绕的方案——考虑使用 email 字段进行自连接（？），对于重复的邮箱记录(1, a@b.com), (3, a@b.com)，自连接就会得到(1, a@b.com, 1, a@b.com), (1, a@b.com, 3, a@b.com), (3, a@b.com, 1, a@b.com), (3, a@b.com, 3, a@b.com)；对于不重复的邮箱记录(2, b@c.com)，自连接只会得到(2, b@c.com, 2, b@c.com)。

因此，我们能够发现规律——如果某个邮箱是重复的，那么它自连接必然会得到这样的记录，即第一个 id 和第二个 id 不相等，而不重复的邮箱必然相等（重复的邮箱会同时生成相等和不等的记录）。

如此，我们便能够得到相应的 SQL，注意需要筛选重复的——

SELECT DISTINCT p1.email AS Email FROM 
Person p1 JOIN Person p2 ON p1.email = p2.email
WHERE p1.id != p2.id;

可以，无论是可读性还是性能，都糟糕透顶。

183. Customers Who Never Order

现在有一张消费者表，只包括 ID 和名称；有一张订单表，包含 ID 和消费者 ID，现在考虑要找到没有任何对应订单的消费者。

最直接的想法是，根据订单表获取所有有过消费的消费者 ID，然后对每个消费者，检查他是否在这个 ID 集合中即可——

SELECT name AS Customers FROM Customers 
WHERE id NOT IN (SELECT DISTINCT customerId FROM Orders);

但也可以考虑使用外连接——首先将消费者表作为主表，使用 id 作为同订单外连接，这样就得到了每个消费者消费的全部消费记录，而没有消费记录的消费者则只有一条，且订单部分为 NULL，这时候我们就可以按主键分组并在订单表的任意字段调用 COUNT 即可。

SELECT c.name AS Customers 
FROM Customers AS c LEFT JOIN Orders AS o ON c.id = o.customerId 
GROUP BY c.id
HAVING COUNT(customerId) = 0;

这里有个有趣的地方——不是说在使用 GROUP BY 时，SELECT 部分只允许出现三个角色：分组的字段，聚集函数处理的列，以及常数吗？为什么在这里出现了列还能正常地跑呢？

因为，表是通过 JOIN 构建的，而左边的列，即 c.id，c.name 总是一一对应的（c.id 是主键，因此对任意一个 c.id，只能找到同一个 c.name），这性质在 JOIN 后仍旧会保留，因此 c.name 是原子的。

这里也可以认为，在这里当我们使用GROUP BY c.id时，它和GROUP BY c.id, c.name等价。

至于网上说 GROUP BY 对主键使用无意义，这论述是正确的，但是这里处理的表并非是 Customers，而是 Customers 和 Orders 进行连接后得到的临时表，这时候 c.id 的主键性质已经失效了——能找到不同的记录，它们拥有同样的主键。

当然，上面其实不必进行分组和聚集操作——使用外连接时，如果某个消费者的订单处为 NULL，则他必然没有任何消费，因为如此便不会为 NULL，我们可以直接使用 IS NULL 判断——

SELECT c.name AS Customers
FROM Customers AS c LEFT JOIN Orders AS o ON c.id = o.customerId 
WHERE customerId IS NULL

196. Delete Duplicate Emails

一图胜千言。

在我尝试了无数次后，我突然发现，这题是删除！真该仔细看看英文，下面也有老外和我犯了一样的错误 hh。

但这题确实有够恶心，让人不禁想问，这真的是 Easy 难度？

这题的表结构和 182 一样，但要求删除重复的邮箱，只保留 ID 最小的。

问题就变得很容易——考虑将邮箱按邮箱路径分组，并找到每组的最小 ID，对该组的邮箱，删除大于该最小 ID 的邮箱即可。这里显然需要使用关系子查询。

DELETE FROM Person
WHERE id > (SELECT MIN(id) FROM Person AS p2 
              WHERE email = p2.email 
              GROUP BY email); 

但问题其实没那么容易……MySQL 好像不支持在 UPDATE（DELETE）子句中的关联子查询中引用自己……这确实会让实现变得非常复杂。

想了想，直接跳过这题，它一点都不明显，甚至用的语法我都没见过/吐血，神 TM 在 DELETE 里用连接，会这个的只有数据库 guru 吧。

197. Rising Temperature

有一张日期——温度表，记录每天的温度；现在考虑计算出温度比前一天高的日期。

该问题比较简单——将某日的日期和它的昨日做连接，筛选出大于昨日的温度的日期即可。

SELECT today.id AS id FROM 
Weather today JOIN Weather yesterday 
  ON today.recordDate = date_add(yesterday.recordDate, INTERVAL 1 DAY)
WHERE today.temperature > yesterday.temperature;

这里唯一需要注意的是日期需要用相应的函数来进行处理，不要试图写什么date + 1 还期待它能够理解。

595. Big Countries

单纯的 WHERE 而已，跳过。

596. Classes More Than 5 Students

现在有一个学生——班级表，表结构为——

Courses table:
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| student     | varchar |
| class       | varchar |
+-------------+---------+

其中(student, class)为主键（就像关系表），现在考虑筛选出有大于等于 5 个学生的班级。

非常简单，按 class 分组，使用 COUNT 做分组的筛选条件即可。

SELECT class FROM Courses GROUP BY class HAVING COUNT(*) >= 5;

620. Not Boring Movies

电影无不无聊我不知道，反正这一题挺无聊。

SELECT * FROM Cinema 
WHERE id % 2 = 1 AND description != 'boring' 
ORDER BY rating DESC;

627. Swap Salary

现在有一张雇员表，它有一个性别字段，使用’f’和’m’来标识性别。现在，我们要给所有人都变个性。

这题考查了“关联”的 UPDATE 语句——我们可以在 SET 子句SET field = expr中的 expr 中引用当前行的字段，

UPDATE Salary
SET sex = CASE sex WHEN 'f' THEN 'm' ELSE 'f' END;

注意，SET 子句中的=并非是等于运算符，而是某种“赋值运算符”，其行为和命令式编程语言中的=一致。

这道题目的测试用例有问题，导致不熟悉语法的我吃了大亏。

1179. Reformat Department Table

这一题需要重点关注一下。

现在有一张部门每月的收入表，表结构如下，其中 id 为部门 id，(id, month)为主键——

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| revenue     | int     |
| month       | varchar |
+-------------+---------+

现在，考虑每个部门（id）从 1 月到 12 月的收入，做成一张横表。

显然，我们必须要按 id 进行分组后再进行其它操作，所以我们先看看分组后会发生什么——

原数据
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

按 id 分组后，会得到——
+-------+------+---------+-------+
| id    | id   | revenue | month |
+-------+------+---------+-------+
| 1     | 1    | 8000    | Jan   |
|       | 1    | 7000    | Feb   |
|       | 1    | 6000    | Mar   |
+-------+------+---------+-------+
| 2     | 2    | 9000    | Jan   |
+-------+------+---------+-------+
| 3     | 3    | 10000   | Feb   |
+-------+------+---------+-------+

在这里，我们想要得到形如1, 8000, 7000, 6000, NULL, ...这样的列，因此我们必须要从分组后的数据中分别选择出 month 为 Jan，Feb，Mar……等的记录的收入。

但是我们做不到，对分组后的数据进行操作，只能使用聚集函数，没法进一步筛选。因此这里只能使用特定的聚集函数来进行该操作，这里符合要求的只有 SUM 了。我们可以在 SUM 中使用 IF 或 CASE 表达式，仅统计所需月份的收入，否则就使用 NULL。

SELECT id,
SUM(IF(month = 'Jan', revenue, NULL)) AS Jan_Revenue,
SUM(IF(month = 'Feb', revenue, NULL)) AS Feb_Revenue,
SUM(IF(month = 'Mar', revenue, NULL)) AS Mar_Revenue
-- ...
FROM Department GROUP BY id;

这里我也试图使用关联子查询，但是这直接超时了……显然这样性能是最优解，记住这个模式。

Medium

178. Rank Scores

现在有一张 ID——成绩表，现在要求按成绩降序进行排名，相同成绩的并列，成绩相邻的排名计数+1，比如，对于下面的输入，有下面的输出——

Input: 
Scores table:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
Output: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

该题显然需要使用窗口函数，观察输出，显然需要使用 DENSE_RANK。

SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS `rank`
FROM Scores;

但是使用窗口函数算什么好汉？有没有别的手段可以用呢？

确实有，答案就是我们要手动模拟窗口函数的执行过程——对每一个记录，检查比它大的集合的数量。根据对应的窗口函数的不同，使用不同的约束方式。

比如，对 RANK，直接统计比它大 score 大的集合的数量即可，SQL 如下——

SELECT score,
  (SELECT (COUNT(*) + 1) FROM Scores AS s1 WHERE s1.score > s0.score) AS `rank`
FROM Scores AS s0 ORDER BY s0.score DESC;

-- 等价于
SELECT score, RANK() OVER (ORDER BY score DESC) FROM Scores;

ROW_NUMBER 就有一些麻烦，需要妥善处理相等的情况。

SELECT 
  score,
  (SELECT (COUNT(*) + 1) FROM Scores AS s1 WHERE 
    s1.score > s0.score OR (s1.score = s0.score AND s1.id > s0.id))
  AS `rank`
FROM Scores AS s0 ORDER BY s0.score DESC, s0.id DESC;

-- 等价于
SELECT score, ROW_NUMBER() OVER (ORDER BY score DESC) FROM Scores;

这里计数分了两个部分——大于当前分数的部分和等于当前分数的部分，大于的部分需要全部统计，等于的部分只需要统计其中一部分（相同的组里 ROW_NUMBER 仍旧是递增的），统计哪部分随意，只要排序后结果正确即可。

DENSE_RANK 的话，它不看前面具体有多少个分数高于它，只看有多少“种”分数高于它，在 RANK 中的 COUNT 中加上 DISTINCT 约束即可。

SELECT score,
  (SELECT (COUNT(DISTINCT s1.score) + 1) FROM Scores AS s1 WHERE s1.score > s0.score) AS `rank`
FROM Scores AS s0 ORDER BY s0.score DESC;

176. Second Highest Salary

现在有一张 ID——工资表，要求获取第二（DISTINCT 的）高的工资，如果没有第二高的工资，则返回 NULL。

考虑到有返回 NULL 的需求，感觉就可以使用连接操作了，进行自连接，右外连接，假设左表为 lower_t，右表为 higher_t，条件设为lower_t.salary <= higher_t.salary AND lower_t.id != higher_t.id，取MAX(lower_t.salary)即可。

SELECT MAX(lower_t.salary) AS SecondHighestSalary
FROM Employee AS lower_t RIGHT JOIN Employee AS higher_t 
ON (lower_t.salary < higher_t.salary);

记住，使用左外连接时，右边的值可能为 NULL，使用右外连接时，左边的值可能为 NULL。

这样写性能颇为灾难。第二种方法就是经典的排序了，DISTINCT 并用 LIMIT 取第二个值，取到了就返回它，否则返回 NULL。

# Write your MySQL query statement below
SELECT 
(SELECT DISTINCT salary 
  FROM Employee ORDER BY salary DESC LIMIT 1, 1) 
AS SecondHighestSalary;

外层的 SELECT 能够把空表转换成 NULL。

第三种方式是，直接筛掉最高的工资，再次调用 MAX 就是第二高的工资了。

SELECT MAX(salary) AS SecondHighestSalary FROM Employee 
WHERE salary != (SELECT MAX(salary) FROM Employee)

177. Nth Highest Salary

和上题类似，但获取第 N 高的工资。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SELECT (N - 1) INTO N;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT salary 
        FROM Employee ORDER BY salary DESC LIMIT N, 1
  );
END

第一想法是，直接拿上一题的代码过来不就好了，这里需要注意 N 需要减 1（偏移量从 0 开始计数），LIMIT 子句里不允许对偏移量和列数进行运算，所以直接修改变量。

另外的想法就是利用窗口函数了，DENSE_RANK 显然满足要求。确定完 rank 后，直接用相等判就好了。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SELECT (N - 1) INTO N;
  RETURN (
      SELECT t.salary FROM 
      (SELECT salary, 
              DENSE_RANK() OVER (ORDER BY salary DESC) AS `rank` 
           FROM Employee) AS t
      WHERE t.rank = N + 1 LIMIT 1
  );
END

184. Department Highest Salary

现在有一个雇员表，一个部门表，部门表包含部门 ID 和名称，雇员表包含雇员 ID，名称，薪水，部门 ID。

现在想要知道每个部门中薪水最高的人的信息，需注意的是一个部门中薪水最高的人不止一个。要做的第一件事是明确的——将两个表进行内连接。

然后这时我们就想着，可以对于每个雇员，检查他所处的部门的最高的薪水，检查他的薪水是否和这最高薪水相等，如果相等就说明他是最高的，将其插入到结果中。这里使用了关联子查询，所以效率很差。

SELECT d.name AS Department, e.name AS Employee, salary
FROM Employee AS e JOIN Department AS d ON e.departmentId = d.id
WHERE salary = (SELECT MAX(salary) FROM Employee AS e0 
                   WHERE e0.departmentId = e.departmentId);

但我们又想着，为啥要对每个雇员分别去查他的部门呢？直接预先把所有部门的最高薪水都查到，在比较时连部门带薪水一起比较不就好了！

SELECT d.name AS Department, e.name AS Employee, salary
FROM Employee AS e JOIN Department AS d ON e.departmentId = d.id
WHERE (d.id, e.salary) IN (
  SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId
);

没想到 SQL 还能进行这种非原子的比较，很酷炫。

直奔评论区，发现有人对 Employee 表，用 RANK 函数直接获得每个部门的薪水的排名，然后从中筛选出排名为 1 的 Employee，使用这些 Employee 对 Department 进行 JOIN。

SELECT d.name AS Department, e.name AS Employee, salary
FROM (SELECT *, 
       RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) 
      AS rk FROM Employee) 
    AS e JOIN Department AS d ON e.departmentId = d.id 
WHERE e.rk = 1;

但这些方法其实性能都差不多，怎么清晰怎么来吧。

180. Consecutive Numbers

现在，有一张 ID——数字表，要求获取这样的数字，它连续出现超过三次，输入输出类似这样——

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

这里，只有数字 1 连续出现三次，选择它。

这题，直接使用特定窗口函数吧……有一个 LEAD 函数和 LAG 函数，LEAD 函数获取向前看 n 行的数据，LAG 函数获取向后看 n 行的数据，这里往前后分别看一行，如果前后一行和当前行相等，说明三行相等，符合要求。

SELECT DISTINCT num AS ConsecutiveNums
FROM (
SELECT num,
    LEAD(num) OVER (ORDER BY id) AS `lead`,
    LAG(num) OVER (ORDER BY id) AS `lag`
FROM Logs
) AS t
WHERE num=`lead` and num=`lag`;

官方解答进行了两次自连接，且不能处理所有情况，很残废。

626. Exchange Seats

现在有一个学生的座位 ID——名称表，要求每个座位为偶数的和他之前的奇数位学生交换座位，如果总座位数为奇数，则最后一个学生不移动，输入和输出如下。

Input: 
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
Output: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+

这其实是一个数学题，我们首先能发现，将IF(ID % 2 = 0, id - 1, id + 1)座位新 ID 进行排序的话，学生的顺序就符合要求了，但最后一个 ID 如果是奇数的话会出现错误，那既然这样的话我们直接按照这个顺序上一个 ROW_NUMBER 不就好了？

SELECT 
  ROW_NUMBER() OVER (ORDER BY IF(id % 2 = 0, id - 1, id + 1)) AS id,
  student 
FROM Seat;

官方推荐的解决方式是在执行之前获取表记录的数量，若是奇数就对最后一个值去特别处理，这相当于是修改了新 ID 的计算方式，这里不表了。

Hard

185. Department Top Three Salaries

和 184. Department Highest Salary 一样，但是要求获取前三高（DISTINCT 的）的薪资。

最开始的想法是，可以直接按上一题使用窗口函数的方法进行修改，判断 rank 时使用[1,3]。

SELECT d.name AS Department, e.name AS Employee, salary
FROM (SELECT *, 
       DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) 
      AS rk FROM Employee) 
    AS e JOIN Department AS d ON e.departmentId = d.id 
WHERE e.rk <= 3;

这代码性能不太好（我不服，tnnd 有人和我的代码一样，怎么性能比我快一倍？怀疑是他们的硬件原因），官方的 Solution 是，对每一个雇员，检查同部门的比它高的薪水的“种类”（DISTINCT），如果这“种类”小于 3，则该雇员的薪水在前三中。这和上一题的关联子查询类似。

SELECT d.name AS Department, e.name AS Employee, salary
FROM Employee AS e JOIN Department AS d ON e.departmentId = d.id
WHERE 3 > (SELECT COUNT(DISTINCT salary) FROM Employee e1
             WHERE e1.departmentId = e.departmentId
               AND e1.salary > e.salary);

可见，这种求第 N 个排名的题基本都可以用关联子查询或窗口函数来解决，其中窗口函数显然是更加通俗易懂的。

262. Trips and Users

这题其实很简单，但却麻烦了我半天……最大的问题是审题不仔细，然后对某些函数不熟悉。

现在有一张出租车订单表和一张用户表，出租车订单表中包含用户 id 和驾驶员 id，两 id 都引用用户表，还包含订单的状态——完成或取消；用户表包含用户的角色以及是否被 ban。

现在，要求获取 2013-10-01 到 2013-10-03 日期中的每天的，用户和驾驶员均未被 ban 的，订单的取消率。

问题其实很简单——筛选出驾驶员和用户未被 ban 的订单，按天分组，统计状态为取消的订单数除以总订单数，格式化即可。

SELECT request_at AS Day, 
       ROUND(1 - (COUNT(IF(status = "completed", 1, NULL)) / COUNT(*)), 2) AS `Cancellation Rate`
FROM Trips WHERE 
    request_at BETWEEN "2013-10-01" AND "2013-10-03"
AND client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')   
AND driver_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
GROUP BY request_at;

601. Human Traffic of Stadium

最后一题了！

现在有一张体育场入场数表，表示每天体育场的入场人数，它长这样——

Stadium table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

参观日期递增，id 和日期一一对应且递增，现在要求获取这些记录——参观人数大于等于 100 且超过连续 3 个 id 如此，比如对上面的表，会选择 5，6，7，8，因为这四个 id 相邻且人数连续大于等于 100.

这题直接使用窗口函数就行了，对每一条记录，我们要检查它以及其上两条，它以及上下一条，它以及其下两条这三个集合中的最小值大于等于 100，只要其中一个满足，它就是符合要求的。

这里使用 ROW 来取得前后的数据，需注意如果前后没有数据，它就干脆不取了，所以需要保证传递给 MIN 的数据为 3 个。

-- 好工整啊
-- 因为 id 本来就是 ASC 的，这里就不用加了
SELECT id, visit_date, people FROM (SELECT id, visit_date, people, 
     MIN(people) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) >= 100 
    AND COUNT(*) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) = 3
  OR MIN(people) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) >= 100 
    AND COUNT(*) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3
  OR MIN(people) OVER (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) >= 100 
    AND COUNT(*) OVER (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) = 3 AS flag  
FROM Stadium) AS t WHERE flag;

可以看到，窗口函数真的能够玩很多花活，且格式非常灵活，之后还得继续了解。

---

下一步是牛客网的82道题！