601-体育馆的人流量

601-体育馆的人流量

1. 题目

2. 思路

思路：查询Stadium表中人流量超过100的记录，将查询结果与自身的临时表连接，再使用where获得满足条件的记录

1. 查询Stadium表中人流量超过100的记录

   select t1.*
   from Stadium t1, Stadium t2, Stadium t3
   where t1.people >= 100 and t2.people >= 100 and t3.people >= 100;
   

   查询结果如下：

   id	visit_date	people
   2	2017-01-02	109
   3	2017-01-03	150
   5	2017-01-05	145
   6	2017-01-06	1455
   7	2017-01-07	199
   8	2017-01-09	188
   2	2017-01-02	109
   3	2017-01-03	150
   5	2017-01-05	145
   6	2017-01-06	1455
   7	2017-01-07	199
   8	2017-01-09	188
   2	2017-01-02	109
   3	2017-01-03	150
   5	2017-01-05	145
   6	2017-01-06	1455
   7	2017-01-07	199
   8	2017-01-09	188
   2	2017-01-02	109
   3	2017-01-03	150
   5	2017-01-05	145
   6	2017-01-06	1455
   7	2017-01-07	199
   8	2017-01-09	188
   2	2017-01-02	109
   3	2017-01-03	150
   5	2017-01-05	145
   6	2017-01-06	1455
   7	2017-01-07	199
   8	2017-01-09	188
   2	2017-01-02	109
   3	2017-01-03	150
   5	2017-01-05	145
   6	2017-01-06	…

2. 添加id连续的三行或更多记录

   select t1.*
   from Stadium t1, Stadium t2, Stadium t3
   where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
   # 添加id连续的三行或更多记录
   and
   (
     (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id = 1)  # t1 t2 t3
     or
     (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id = 1)  # t2 t1 t3
     or
     (t3.id - t1.id = 2 and t3.id - t2.id = 1 and t2.id - t1.id = 1)  # t3 t2 t1
   );
   

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

3. distinct去重
   

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

4. 根据id排序

   
   

3. 解决

# Write your MySQL query statement below
# 思路：查询Stadium表中人流量超过100的记录，将查询结果与自身的临时表连接，再使用where获得满足条件的记录

# 第一步：查询人流量超过100的记录
select distinct t1.*
from Stadium t1, Stadium t2, Stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
# 添加id连续的三行或更多记录
and
(
  (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id = 1)  # t1 t2 t3
  or
  (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id = 1)  # t2 t1 t3
  or
  (t3.id - t1.id = 2 and t3.id - t2.id = 1 and t2.id - t1.id = 1)  # t3 t2 t1
)
order by t1.id;

4. 运行结果