方法一
select t1.* from stadium t1, stadium t2, stadium t3 where t1.people >= 100 and t2.people >= 100 and t3.people >= 100 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 - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1 ) ;
方法二
with people as ( select id, visit_date, people, Lag(people,2) over(order by id) as pprvPeople, Lag(people,1) over(order by id) as prvPeople, Lead(people,1) over(order by id) as nextPeople, Lead(people,2) over(order by id) as nnextPeople from stadium ) select id, visit_date, people from people where (people >= 100 and prvPeople>=100 and pprvPeople>=100) || (people >= 100 and nextPeople>=100 and nnextPeople>=100) || (people >= 100 and nextPeople>=100 and prvPeople>=100) ;
方法三
with t as ( select id,visit_date,people,cast(r as signed)-id df from ( select id,visit_date,people, row_number() over (order by id) r from ( select * from Stadium where people>=100 ) a ) b ) #这里r是unsigned
#这里可以进行优化
with t as ( select id,visit_date,people, id-row_number() over (order by id) rk from Stadium where people>=100 )
select id,visit_date,people from t where df in ( select df from t group by df having count(*)>=3 );