LeetCode601查询体育馆的人流量问题详解(MySQL三种方法实现)

题目如下：

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

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。

例如，表 stadium：

+------+------------+-----------+
| id   | 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   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+
Note:
每天只有一行记录，日期随着 id 的增加而增加。

方法一：使用自关联。

完整代码：

select distinct s1.* from stadium as s1,stadium as s2,stadium as s3 
where 
((s1.id+1=s2.id and s2.id+1=s3.id) or (s1.id+2=s2.id and s2.id-1=s3.id)
or (s1.id-1=s2.id and s2.id+2=s3.id) or (s1.id+1=s2.id and s2.id-2=s3.id)
or (s1.id-1=s2.id and s2.id-1=s3.id) or (s1.id-2=s2.id and s2.id+1=s3.id))
and
(s1.people>=100 and s2.people>=100 and s3.people>=100) order by s1.id;

解析：

SELECT e.id,e.date,e.people FROM
(SELECT id, s.date,
@sdate1 := 
(CASE
WHEN s.people >=100 THEN @sdate1
ELSE @sdate1 + 1
END) AS sdate,s.people FROM  stadium as s,(SELECT @sdate1 := 0) as b)  as e,
(SELECT c.sdate FROM (SELECT id,s.date,
@sdate := 
(CASE
WHEN s.people >= 100 THEN @sdate
ELSE @sdate + 1 END) AS sdate,
s.people FROM stadium as s,(SELECT @sdate := 0) as b) as c WHERE c.people >= 100 
GROUP BY c.sdate HAVING COUNT(c.sdate) >=3) as d
WHERE d.sdate = e.sdate      
AND e.people >= 100;

解析：

select id,date,people from
(select temp.*,f.*,
case
when num = 1 then num
else
(case
when @previd=id-1 then @feature
else @feature:=@feature+1
end)
end as id_feature,@previd:=id
from
(select t.*,@count := @count + 1 as num from (select * from stadium where people >=
100) as t,(select @count := 0) as count) as temp,
(select @previd :=0,@feature := 1) as f) as new_temp
where id_feature in
(select id_feature1 from
(select temp1.*,f1.*,
case
when num1 = 1 then num1
else
(case
when @previd1=id-1 then @feature1
else @feature1:=@feature1+1
end)
end as id_feature1,@previd1:=id
from
(select t1.*,@count1 := @count1 + 1 as num1 from (select * from stadium where people
>= 100) as t1,(select @count1 := 0) as count1) as temp1,
(select @previd1 :=0,@feature1 := 1) as f1) as new_temp1 group by id_feature1 
having count(id_feature1)>=3);

解析：

思路：同样是添加辅助列，但与方法二不同的是。要先从stadium表中筛选出people不小于100的信息记录，
然后在筛选结果中添加一列用于表示id特征的辅助列。
对于本题来说，就是先从
id    people    
1     10       
2     109       
3     150       
4     99        
5     145       
6     1455      
7     199       
8     188 中筛选出：id    people          
2     109       
3     150              
5     145       
6     1455      
7     199       
8     188 然后，根据id的是否连续添加一列辅助列：
id    people    id_feature       
2     109       1
3     150       1      
5     145       2
6     1455      2
7     199       2
8     188       2注：基本思想就是每一组连续id都对应一个相同的@feature值，更多数据的示例如下：
id    people    id_feature          
2     109       1
3     150       1      
5     145       2
6     1455      2
7     199       2
8     188       2
15    182       3
16    189       3
28    301       4
29    556       4
30    412       4在此基础上，根据id_feature的值对数据进行分组，并根据每组内id_feature值的统计个数是否不小于3确定
满足查询要求的id_feature值，然后以这些id_feature值为条件，进一步筛选出符合查询要求的id,date,people信息。

方法三与方法二相比，添加至stadium表的辅助列中的值更容易想到，但实现辅助列内的值的代码也更复杂(有一段重复用到的代码)，需要用到嵌套的case when函数，这是笔者的想法，如果有朋友有更加容易的实现方法，还请不吝赐教！

参考：

http://www.cnblogs.com/linux-java/p/9766433.html

PS：本文为博主原创文章，转载请注明出处。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！