题目:
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;
结果显示是这样,与预期相符:
题目随简单, 依然需要我们用心面对, 才能得出正确的结果