Leetcode-mysql连续多行自记录查询

题目:

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

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

按照题目要求, 我用sql建表导入数据:

drop table if exists stadium;
create table stadium(id int(100), visit_date timestamp, people int(100))
truncate stadium;
insert into  stadium values(1,'2017-01-02',10);
insert into  stadium values(2,'2017-01-02',109);
insert into  stadium values(3,'2017-01-03',150);
insert into  stadium values(4,'2017-01-04',99);
insert into  stadium values(5,'2017-01-05',145);
insert into  stadium values(6,'2017-01-06',1455);
insert into  stadium values(7,'2017-01-07',199);
insert into  stadium values(8,'2017-01-08',188);
select * from stadium;

建完之后查询数据应该是这样的:

解题思路:

1.先找出记录中的人流量不少于100

2.再找出连续三行的记录数

第一次写出的sql是这样的:

select 
distinct a.*
from stadium as a, stadium as b , stadium as c
where (a.id = b.id-1 and b.id+1=c.id)
and (a.people>=100 and b.people>=100 and c.people>=100)
order by a.id;

结果发现只有两行记录, 而结果用肉眼都能看出这是错的,

后来发现原因是我将 a 的 id 设为三个连续值中最小值，所以只返回了每 3 个连续值中最小的一个 ,所有应当把a.id的最小值,中间值和最大值都考虑到,后来就写成这样:

select 
distinct a.*
from stadium as a, stadium as b , stadium as 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.people>=100 and c.people>=100)
order by a.id;

结果显示是这样,与预期相符:

题目随简单, 依然需要我们用心面对, 才能得出正确的结果