数据合并，能用一条SQL得到吗？

Question

在下有一个问题，想把id相同，并且时间是连续的记录合并成一条记录，如下所示
with aa(id,qsrq,zzrq) as(
with aa(id,qsrq,zzrq) as(
select 1,date '2010-01-01',date '2010-01-31' from dual union
select 1,date '2010-02-01',date '2010-02-28' from dual union
select 1,date '2010-03-01',date '2010-03-31' from dual union
select 1,date '2010-04-01',date '2010-04-30' from dual union
select 1,date '2010-05-01',date '2010-05-31' from dual union
select 1,date '2010-07-01',date '2010-07-31' from dual union
select 2,date '2010-01-01',date '2010-01-31' from dual union
select 2,date '2010-02-01',date '2010-02-28' from dual union
select 2,date '2010-03-01',date '2010-03-31' from dual union
select 3,date '2010-04-01',date '2010-04-30' from dual union
select 4,date '2010-05-01',date '2010-05-31' from dual union
select 5,date '2010-07-01',date '2010-07-31' from dual )  最后想得到
id，qsrq,zzrq
1,2010-01-01,2010-05-31
1,2010-07-01,2010-07-31
2,2010-01-01,2010-03-31
3,2010-04-01,2010-04-30
4,2010-05-01,2010-05-31
5,2010-07-01,2010-07-31
即时间是连续的记录合并在一起

Answer 1

with aa  as(
select 1 id,'2010-01-01' qsrq,'2010-01-31' zzrq from dual union
select 1, '2010-02-01', '2010-02-28' from dual union
select 1, '2010-03-01', '2010-03-31' from dual union
select 1, '2010-04-01', '2010-04-30' from dual union
select 1, '2010-05-01', '2010-05-31' from dual union
select 1, '2010-07-01', '2010-07-31' from dual union
select 2, '2010-01-01', '2010-01-31' from dual union
select 2, '2010-02-01', '2010-02-28' from dual union
select 2, '2010-03-01', '2010-03-31' from dual union
select 3, '2010-04-01', '2010-04-30' from dual union
select 4, '2010-05-01', '2010-05-31' from dual union
select 5, '2010-07-01', '2010-07-31' from dual 
)
Select Id, Min(Qsrq), Max(Zzrq)
  From (Select Id,
               Qsrq,
               Zzrq,
               To_Char(To_Date(Zzrq, 'yyyy-mm-dd'), 'mm') -
               (Lag(To_Char(To_Date(Zzrq, 'yyyy-mm-dd'), 'mm'), 1, '00')
                Over(Partition By Id Order By Id)) Lm
          From Aa) t
 Group By Id, t.Lm
 Order By Id写了一个，期待高人

Answer 2

with aa as(
select 1 as id,date '2010-01-01' as bdate,date '2010-01-31' as edate from dual union
select 1,date '2010-02-01',date '2010-02-28' from dual union
select 1,date '2010-03-01',date '2010-03-31' from dual union
select 1,date '2010-04-01',date '2010-04-30' from dual union
select 1,date '2010-05-01',date '2010-05-31' from dual union
select 1,date '2010-07-01',date '2010-07-31' from dual union
select 2,date '2010-01-01',date '2010-01-31' from dual union
select 2,date '2010-02-01',date '2010-02-28' from dual union
select 2,date '2010-03-01',date '2010-03-31' from dual union
select 3,date '2010-04-01',date '2010-04-30' from dual union
select 4,date '2010-05-01',date '2010-05-31' from dual union
select 4,date '2010-06-01',date '2010-06-30' from dual union
select 4,date '2010-08-01',date '2010-08-31' from dual union
select 5,date '2010-07-01',date '2010-07-31' from dual  
) 
select aa.id,min(aa.bdate),max(aa.edate)
from (
select aa.id,aa.bdate,aa.edate,lag(aa.edate+1)over(partition by aa.id order by aa.id) as ndate,
nvl(aa.bdate-lag(aa.edate+1)over(partition by aa.id order by aa.id),0) as cha 
from aa order by aa.id,aa.bdate
) aa
group by aa.id,aa.cha结果如下:
ID MIN(AA.BDATE) MAX(AA.EDATE)
1 2010-01-01 2010-05-31
1 2010-07-01 2010-07-31
2 2010-01-01 2010-03-31
3 2010-04-01 2010-04-30
4 2010-05-01 2010-06-30
4 2010-08-01 2010-08-31
5 2010-07-01 2010-07-31

Answer 3

with aa as(
select 1 id,to_date('2010-01-01','yyyy-mm-dd') qsrq,to_date('2010-01-31','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-02-01','yyyy-mm-dd') qsrq,to_date('2010-02-28','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-03-01','yyyy-mm-dd') qsrq,to_date('2010-03-31','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-04-01','yyyy-mm-dd') qsrq,to_date('2010-04-30','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-05-01','yyyy-mm-dd') qsrq,to_date('2010-05-31','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-07-01','yyyy-mm-dd') qsrq,to_date('2010-07-31','yyyy-mm-dd') zzrq from dual union
select 2 id,to_date('2010-01-01','yyyy-mm-dd') qsrq,to_date('2010-01-31','yyyy-mm-dd') zzrq from dual union
select 2 id,to_date('2010-02-01','yyyy-mm-dd') qsrq,to_date('2010-02-28','yyyy-mm-dd') zzrq from dual union
select 2 id,to_date('2010-03-01','yyyy-mm-dd') qsrq,to_date('2010-03-31','yyyy-mm-dd') zzrq from dual union
select 3 id,to_date('2010-04-01','yyyy-mm-dd') qsrq,to_date('2010-04-30','yyyy-mm-dd') zzrq from dual union
select 4 id,to_date('2010-05-01','yyyy-mm-dd') qsrq,to_date('2010-05-31','yyyy-mm-dd') zzrq from dual union
select 5 id,to_date('2010-07-01','yyyy-mm-dd') qsrq,to_date('2010-07-31','yyyy-mm-dd') zzrq from dual  
)
select id,min(qsrq),max(zzrq) from(
select id,qsrq,zzrq,case when qsrq = nextm or nextm is null then 0 else 1 end flag from(
select id,qsrq,zzrq,lag(zzrq+1) over(partition by id order by id) nextm from aa
)
) group by id,flag order by id,min(qsrq)

Answer 4

6,7楼的好象都还存在着一个问题，如果我再加入一条
select 1 id,to_date('2010-08-01','yyyy-mm-dd') qsrq,to_date('2010-08-30','yyyy-mm-dd') zzrq from dual union
出来的结果就不对了
1 01-1月 -10 30-8月 -10
1 01-7月 -10 31-7月 -10
2 01-1月 -10 31-3月 -10
3 01-4月 -10 30-4月 -10
4 01-5月 -10 31-5月 -10
5 01-7月 -10 31-7月 -10

Answer 5

--我比较喜欢建表
create table aa
(
  id int,
  qsrq varchar2(10),
  zzrq varchar2(10)
)
--请原谅我不用DATE类型，我不喜欢
insert into aa  select 1, '2010-01-01', '2010-01-31' from dual union
select 1,  '2010-02-01',  '2010-02-28' from dual union
select 1,  '2010-03-01',  '2010-03-31' from dual union
select 1,  '2010-04-01',  '2010-04-30' from dual union
select 1,  '2010-05-01',  '2010-05-31' from dual union
select 1,  '2010-07-01',  '2010-07-31' from dual union
select 2,  '2010-01-01',  '2010-01-31' from dual union
select 2,  '2010-02-01',  '2010-02-28' from dual union
select 2,  '2010-03-01',  '2010-03-31' from dual union
select 3,  '2010-04-01',  '2010-04-30' from dual union
select 4,  '2010-05-01',  '2010-05-31' from dual union
select 5,  '2010-07-01',  '2010-07-31' from dual  --
with cc as 
(select id,dt from (
select id,
       to_date(zzrq, 'yyyy-MM-dd') - to_date(qsrq, 'yyyy-MM-dd') as num, 
       bb.lv,to_date(qsrq, 'yyyy-MM-dd')+bb.lv-1 as dt
  from aa, (select level as lv from dual connect by level < 32) --这里的LEVEL可以设置再大点，根据你业务需求，
  bb                                                            --因为我不知道你的zzrq 与 qsrq的差值最大是多少
   order by id, to_date(qsrq, 'yyyy-MM-dd')+bb.lv-1
   ) t where t.lv<=num+1)select id,min(dt) as dt1,max(dt) as dt2   from(
select cc.*,dt-row_number() over (partition by id order by dt) as rn from cc 
)
group by id,rn
--新手所写，莫见怪
--PS：您给的数据不具备一般代表性啊，所以造数据的时候造好一点，多考虑点您想要的特殊效果，和存在的特殊情况
--我的SQL思想是，化简为繁(这好像也是数学里面的一种思想)

Answer 6

with aa as(
select 1 as id,date '2010-01-01' as qsrq,date '2010-01-31' as Zzrq from dual union
select 1,date '2010-02-01',date '2010-02-28' from dual union
select 1,date '2010-03-01',date '2010-03-31' from dual union
select 1,date '2010-04-01',date '2010-04-30' from dual union
select 1,date '2010-05-01',date '2010-05-31' from dual union
select 1,date '2010-07-01',date '2010-07-31' from dual union
select 1,date '2010-08-01',date '2010-08-31' from dual union
select 1,date '2010-10-01',date '2010-10-31' from dual union
select 2,date '2010-01-01',date '2010-01-31' from dual union
select 2,date '2010-02-01',date '2010-02-28' from dual union
select 2,date '2010-03-01',date '2010-03-31' from dual union
select 3,date '2010-04-01',date '2010-04-30' from dual union
select 4,date '2010-05-01',date '2010-05-31' from dual union
select 4,date '2010-06-01',date '2010-06-30' from dual union
select 4,date '2010-08-01',date '2010-08-31' from dual union
select 5,date '2010-07-01',date '2010-07-31' from dual  
) 
Select Id, Min(qsrq),Max(zzrq) From (
Select Id,
       Qsrq,
       Zzrq,
       To_Char(Zzrq, 'mm') m,
        Row_Number() Over(Partition By Id Order By Qsrq) rn
  From Aa)
Group By Id, to_number(m) - rn
Order By Id

Answer 7

with aa as(
select 1 AS id,date '2010-01-01' AS qsrq,date '2010-01-31' as zzrq from dual union
select 1,date '2010-02-01',date '2010-02-28' from dual union
select 1,date '2010-03-01',date '2010-03-31' from dual union
select 1,date '2010-04-01',date '2010-04-30' from dual union
select 1,date '2010-05-01',date '2010-05-31' from dual union
select 1,date '2010-07-01',date '2010-07-31' from dual union
select 2,date '2010-01-01',date '2010-01-31' from dual union
select 2,date '2010-02-01',date '2010-02-28' from dual union
select 2,date '2010-03-01',date '2010-03-31' from dual union
select 3,date '2010-04-01',date '2010-04-30' from dual union
select 4,date '2010-05-01',date '2010-05-31' from dual union
select 5,date '2010-07-01',date '2010-07-31' from dual  )
SELECT DISTINCT A.ID
  ,DECODE(A.F1,1,A.QSRQ,LAG(A.QSRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS QSRQ
  ,DECODE(A.F2,1,A.ZZRQ,LEAD(A.ZZRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS ZZRQ
FROM (
SELECT AA.*
,DECODE(AA.QSRQ -LAG(AA.ZZRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) ,1,0,1) AS F1
,DECODE(LEAD(AA.QSRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) - AA.ZZRQ,1,0,1) AS F2
,LAG(AA.ZZRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) -AA.QSRQ
FROM AA) A
WHERE F1+F2 > 0
ORDER BY 1,2

Answer 8

with aa as(
select 1 id,to_date('2010-01-01','yyyy-mm-dd') qsrq,to_date('2010-01-31','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-02-01','yyyy-mm-dd') qsrq,to_date('2010-02-28','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-03-01','yyyy-mm-dd') qsrq,to_date('2010-03-31','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-04-01','yyyy-mm-dd') qsrq,to_date('2010-04-30','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-05-01','yyyy-mm-dd') qsrq,to_date('2010-05-31','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-07-01','yyyy-mm-dd') qsrq,to_date('2010-07-31','yyyy-mm-dd') zzrq from dual union
select 1 id,to_date('2010-08-01','yyyy-mm-dd') qsrq,to_date('2010-08-31','yyyy-mm-dd') zzrq from dual union
select 2 id,to_date('2010-01-01','yyyy-mm-dd') qsrq,to_date('2010-01-31','yyyy-mm-dd') zzrq from dual union
select 2 id,to_date('2010-02-01','yyyy-mm-dd') qsrq,to_date('2010-02-28','yyyy-mm-dd') zzrq from dual union
select 2 id,to_date('2010-03-01','yyyy-mm-dd') qsrq,to_date('2010-03-31','yyyy-mm-dd') zzrq from dual union
select 3 id,to_date('2010-04-01','yyyy-mm-dd') qsrq,to_date('2010-04-30','yyyy-mm-dd') zzrq from dual union
select 4 id,to_date('2010-05-01','yyyy-mm-dd') qsrq,to_date('2010-05-31','yyyy-mm-dd') zzrq from dual union
select 5 id,to_date('2010-07-01','yyyy-mm-dd') qsrq,to_date('2010-07-31','yyyy-mm-dd') zzrq from dual 
)
select id,min(qq),max(zq) from(
select id,min(qsrq) qq,max(zzrq) zq,sum(flag) over(order by rown) flag from(
select id,qsrq,zzrq,case when flag = 1 or flag1 = 1 then 1 else 0 end flag,rown from(
select id,qsrq,zzrq,case when nm is null then 1 else 0 end flag1,flag,rownum rown from(
select id,qsrq,zzrq,lag(qsrq) over(partition by id order by id) nm,
case when lag(zzrq+1) over(order by id) = qsrq then 0 else 1 end flag from aa
)
)
)
group by id,flag,rown
) group by id, flag order by id

Answer 9

我的SQL完全正确啊？被无视了？with aa as(
select 1 AS id,date '2010-01-01' AS qsrq,date '2010-01-31' as zzrq from dual union
select 1,date '2010-02-01',date '2010-02-14' from dual union
select 1,date '2010-02-15',date '2010-03-02' from dual union
select 1,date '2010-03-03',date '2010-03-31' from dual union
select 1,date '2010-04-01',date '2010-04-30' from dual union
select 1,date '2010-05-01',date '2010-06-28' from dual union
select 1,date '2010-06-30',date '2010-07-24' from dual union
select 2,date '2010-01-01',date '2010-01-31' from dual union
select 2,date '2010-02-01',date '2010-02-28' from dual union
select 2,date '2010-03-01',date '2010-03-31' from dual union
select 3,date '2010-04-01',date '2010-04-30' from dual union
select 4,date '2010-05-01',date '2010-05-31' from dual union
select 5,date '2010-07-01',date '2010-07-31' from dual  )
SELECT DISTINCT A.ID
  ,DECODE(A.F1,1,A.QSRQ,LAG(A.QSRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS QSRQ
  ,DECODE(A.F2,1,A.ZZRQ,LEAD(A.ZZRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS ZZRQ
FROM (
SELECT AA.*
,DECODE(AA.QSRQ -LAG(AA.ZZRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) ,1,0,1) AS F1
,DECODE(LEAD(AA.QSRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) - AA.ZZRQ,1,0,1) AS F2
FROM AA) A
WHERE F1+F2 > 0
ORDER BY 1,2结果ID QSRQ ZZRQ
1 2010/01/01 2010/06/28
1 2010/06/30 2010/07/24
2 2010/01/01 2010/03/31
3 2010/04/01 2010/04/30
4 2010/05/01 2010/05/31
5 2010/07/01 2010/07/31