Leetcode之mysql专题《Human Traffic of Stadium》

题目内容如下（链接：https://leetcode.com/problems/human-traffic-of-stadium/submissions/1）

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table stadium:

+------+------------+-----------+
| id   | 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-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

Note:
Each day only have one row record, and the dates are increasing with id increasing.

题解思路如下：

1. 题目要求找出最近连续3天或以上人数超过100的记录，解决方法比较简单，就关联3个相同的表t1、t2、t3，过滤掉人数小于100的记录之后，满足下面3个条件之一即可：

(1) t1.id>t2.id>t3.id 且相邻2者差距为1天

(2) t3.id>t1.id>t2.id 且相邻2者差距为1天

(3) t3.id>t2.id>t1.id 且相邻2者差距为1天

注意最后要对结果进行去重

2. 汇总一下即可得到问题的答案如下

SELECT
    DISTINCT t1.*
FROM
    stadium t1, stadium t2, stadium t3
WHERE
    t1.people >= 100 AND t2.people >= 100 AND t3.people >= 100 AND(
        (t1.id - t2.id = 1 AND t1.id - t3.id = 2) OR
        (t1.id - t2.id = 1 AND t3.id - t1.id = 1) OR
        (t2.id - t1.id = 1 AND t3.id - t1.id = 2)
    )
ORDER BY t1.id;