连续日期

本文主要是介绍连续日期，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

方法一

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 );

这篇关于连续日期的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！