601. 体育馆的人流量
题目描述
X 市建了一个新的体育馆,每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句,找出人流量的高峰期。高峰期时,至少连续三行记录中的人流量不少于100。
例如,表 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 |
对于上面的示例数据,输出为:
id | visit_date | people |
---|---|---|
5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 |
提示:
每天只有一行记录,日期随着 id 的增加而增加。
实现
实现1
左连接,五表连接,右边的四个表分别是第一个表的前第二个、前第一个、后第一个和后第二个。
select a.*
from stadium a left join stadium b on a.id = b.id - 2
left join stadium c on a.id = c.id - 1
left join stadium d on a.id = d.id + 1
left join stadium e on a.id = e.id + 2
where (a.people >= 100 and b.people >= 100 and c.people >= 100) or
(a.people >= 100 and c.people >= 100 and d.people >= 100) or
(a.people >= 100 and d.people >= 100 and e.people >= 100)
order by a.id;
实现2
三表连接,把符合条件的三行连接在一起,但是有可能在同一天有不止一种情况能判断这一天是高峰期,如id=6这一天,所以要用distinct去除重复。
select distinct a.*
from stadium a, stadium b, stadium c
where a.people >= 100 and b.people >= 100 and c.people >= 100 and
((a.id = b.id - 1 and a.id = c.id - 2) or
(a.id = b.id + 1 and a.id = c.id - 1) or
(a.id = b.id + 1 and a.id = c.id + 2))
order by a.id;
实现3
使用case方式。
case
when 第一天:
when 第二天:
when 倒数第二天:
when 最后一天:
其他情况:
end
select *
from stadium s1
where s1.people >= 100 and (case
when s1.id = 1 then (select people from stadium where id = 2) >= 100 and
(select people from stadium where id = 3) >= 100
when s1.id = 2 then ((select people from stadium where id = 1) >= 100 and
(select people from stadium where id = 3) >= 100) or
((select people from stadium where id = 3) >= 100 and
(select people from stadium where id = 4) >= 100)
when s1.id = (select count(*) from stadium) - 1 then
((select people from stadium s2 where s2.id = s1.id - 1) >= 100 and
(select people from stadium s2 where s2.id = s1.id + 1) >= 100) or
((select people from stadium s2 where s2.id = s1.id - 1) >= 100 and
(select people from stadium s2 where s2.id = s1.id - 2) >= 100)
when s1.id = (select count(*) from stadium) then
(select people from stadium s2 where s2.id = s1.id - 1) >= 100 and
(select people from stadium s2 where s2.id = s1.id - 2) >= 100
else ((select people from stadium s2 where s2.id = s1.id - 1) >= 100 and
(select people from stadium s2 where s2.id = s1.id + 1) >= 100) or
((select people from stadium s2 where s2.id = s1.id - 1) >= 100 and
(select people from stadium s2 where s2.id = s1.id - 2) >= 100) or
((select people from stadium s2 where s2.id = s1.id + 1) >= 100 and
(select people from stadium s2 where s2.id = s1.id + 2) >= 100)
end
);