（Creative Commons）
601.Human_Traffic_of_Stadium
https://leetcode.com/problems/human-traffic-of-stadium/
Solution-for-3_or_more consecutive_visit_date
代码语言：javascript复制Create table temp_visit_date
select
b.id,
b.visit_date,
b.people,
DATE_SUB(visit_date,INTERVAL b.rk DAY) as diffdate
from (
select
a.id,
a.visit_date,
a.people,
(select count(distinct visit_date) from stadium b where b.people >= 100 and b.visit_date <= a.visit_date) as rk
from stadium a
where a.people >= 100
order by visit_date ASC
) b;
select
id,
visit_date,
people
from (
select diffdate, count(1) as cnt from temp_visit_date group by diffdate having cnt >= 3
)tt left join temp_visit_date aa on aa.diffdate = tt.diffdate
;Solutionp-for-3_or_more consecutive_rows
代码语言：javascript复制# Write your MySQL query statement below
select
zz.id,
zz.visit_date,
zz.people
from (
select diff_row, count(1) as cnt from (
select
b.id,
b.visit_date,
b.people,
(id-rk) as diff_row
from (
select
a.id,
a.visit_date,
a.people,
(select count(distinct id) from stadium b where b.people >= 100 and b.id <= a.id) as rk
from stadium a
where a.people >= 100
order by id ASC
) b
) xx group by diff_row having cnt >= 3
) tt left join (
select
b.id,
b.visit_date,
b.people,
(id-rk) as diff_row
from (
select
a.id,
a.visit_date,
a.people,
(select count(distinct id) from stadium b where b.people >= 100 and b.id <= a.id) as rk
from stadium a
where a.people >= 100
order by id ASC
) b
) zz on zz.diff_row = tt.diff_row
;
