oracle sql语句连续时间

发布网友发布时间：2022-04-21 18:17

共2个回答

热心网友时间：2022-04-13 18:47

--找出sfzh相同，且持续3天或以上的数据
--测试数据
with a("ID",sfzh,sj) as (
select 1,1,'20130101' from al union all
select 2,1,'20130102' from al union all
select 3,1,'20130103' from al union all
select 4,2,'20130101' from al union all
select 5,2,'20130105' from al union all
select 6,3,'20130101' from al union all
select 7,3,'20130102' from al union all
select 8,3,'20130105' from al)
select
  sfzh,min(sj) as startDate,max(sj) as endDate,count(*) as "length"
from (
  select "ID",sfzh,sj
    ,to_date(sj,'yyyymmdd')-row_number() over (partition by sfzh order by sj) as grp
  from a
) t group by sfzh,grp
having count(*)>=3;

结果：

热心网友时间：2022-04-13 20:05

SELECT A.*
  FROM A ,
  (SELECT MIN(SJ) MI,MAX(SJ) MA,COUNT(*)
     FROM A
    WHERE SFZH IS NOT NULL
    GROUP BY SJ-ROWNUM
    HAVING COUNT(*) > 2) T1
WHERE SJ BETWEEN T1.MI AND T1.MA
ORDER BY A.ID

ORACLE 11G测试通过。