601. 体育馆的人流量

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
                        );