2021-04-22

27. 体育馆人流量

需求：请编写一个查询语句，找出人流量的高峰期。高峰期定义，至少连续三行记录中的人流量不少于100。

展示效果：

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

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

最终SQL:

a b c
b c a
c b a

select
	distinct a.*
from
	27_stadium a,
	27_stadium b,
	27_stadium c
where 
	(a.id = b.id -1 and b.id + 1 = c.id) or (a.id -1 = b.id and a.id + 1 = c.id) or (a.id - 1 = c.id and c.id -1 = b.id)
	and
	(a.people >=100 and b.prople >=100 and c.people >= 100)
order by
	a.id