🔴 601. 体育馆的人流量

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量(people)。
请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。
例如，表 stadium：

对于上面的示例数据，输出为：

Note:
每天只有一行记录，日期随着 id 的增加而增加。

答案

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+1 = b.id and b.id+1 = c.id) or
       (a.id-1 = b.id and a.id+1 = c.id) or
       (a.id-1 = c.id and a.id+1 = b.id) or
       (a.id-2 = b.id and a.id-1 = c.id) or
       (a.id-1 = b.id and a.id-2 = c.id)
      )
order by a.id

笔记

高票答案与我的逻辑一样，但如果需要连续 n 天，没办法灵活改动。
有别人的写法：

SELECT id, `date`, people
FROM (
SELECT id, `date`, people, @m := IF(k >= 3 OR (@n - k >= 0 AND k > 0), 1, 0) AS t, @n := IF(k = 3, 3, @n - k) AS _2, k
FROM (
SELECT id, `date`, people, 
    @i := IF(people >= 100, 1, 0) AS _,
    @j := IF(@i = 1, @j + 1, 0) AS k
FROM stadium , (SELECT @i:=0, @j:=0) __a 
ORDER BY id
) t , (SELECT @m:=0, @n:=0) __a 
ORDER BY id DESC
) a WHERE t = 1
ORDER BY id

可以解决十几天连续超过100人的问题； 引入单个变量； 代码可读性和可迁移性也还行
select stadium.* from stadium
inner join
    (
        select
            id-rownum as diff
            ,group_concat(id) as idlist
        from
            (
                select
                    @rownum:=@rownum+1 as rownum
                    ,id
                from stadium,(select @rownum :=0) t
                where people>=100
            )t
        group by id-rownum
        having length(group_concat(id))-length(replace(group_concat(id),',',''))>=2 #表示至少出现三个id
    )t
    on find_in_set(stadium.id,t.idlist)