连续日期

连续日期

 

 

 连续日期

 

 

 连续日期

 

 

 连续日期

方法一

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 );
上一篇:C++中子类出现与父类同名成员函数如果调用父类函数


下一篇:2021-05-17