[SQL]LeetCode601. 体育馆的人流量 | Human Traffic of Stadium

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/10451553.html 
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

SQL架构

Create table If Not Exists stadium (id int, visit_date DATE NULL, people int)
Truncate table stadium
insert into stadium (id, visit_date, people) values ('1', '2017-01-01', '10')
insert into stadium (id, visit_date, people) values ('2', '2017-01-02', '109')
insert into stadium (id, visit_date, people) values ('3', '2017-01-03', '150')
insert into stadium (id, visit_date, people) values ('4', '2017-01-04', '99')
insert into stadium (id, visit_date, people) values ('5', '2017-01-05', '145')
insert into stadium (id, visit_date, people) values ('6', '2017-01-06', '1455')
insert into stadium (id, visit_date, people) values ('7', '2017-01-07', '199')
insert into stadium (id, visit_date, people) values ('8', '2017-01-08', '188')

---

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, visit_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   | 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-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:

+------+------------+-----------+
| id   | visit_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.

---

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。

例如，表 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       |
+------+------------+-----------+

对于上面的示例数据，输出为：

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

Note:
每天只有一行记录，日期随着 id 的增加而增加。

---

260ms

# Write your MySQL query statement below
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 and t2.id-t3.id=1)
or
(t2.id-t1.id=1 and t2.id-t3.id=2 and t1.id-t3.id=1)
or
(t3.id-t2.id=1 and t2.id-t1.id=1 and t3.id-t1.id=2))
order by t1.id

---

266ms

# Write your MySQL query statement below

select distinct t.* from 
stadium t ,
(
select 
t1.Id, t2.Id as t2Id, t3.Id as t3Id
from (
select * from stadium 
where people>=100)  as t1
left join
(
select * from stadium 
where people>=100 ) as t2
ON  t1.Id+1=t2.Id
left join 
(
select * from stadium 
where people>=100 ) as t3
ON  t1.Id+2=t3.Id
where (t2.Id is not null and t3.Id is not null)
) as b 
where (t.Id=b.Id or t.Id=b.t2Id or t.Id=b.t3Id);

---

268ms

# Write your MySQL query statement below
SELECT s1.id AS id
      ,s1.date AS date
      ,s1.people AS people
FROM stadium s1, stadium s2, stadium s3
WHERE(
    (s1.id = s2.id - 1 AND s1.id = s3.id - 2)
    OR (s1.id = s2.id + 1 AND s1.id = s3.id - 1)
    OR (s1.id = s2.id + 1 AND s1.id = s3.id +2)
    )
AND s1.people >= 100
AND s2.people >= 100
AND s3.people >= 100
GROUP BY s1.id;