oracle 多条是否连续,急多条记录 连续日期的 合并有关问题

SQL codeSQL> WITH t AS (

2 SELECT 1 seq,'AA' tname,TO_DATE('2009-01-01','yyyy-mm-dd') start_date,TO_DATE('2009-07-31','yyyy-mm-dd') end_date FROM DUAL UNION ALL

3 SELECT 2 seq,'AA' tname,TO_DATE('2009-08-02','yyyy-mm-dd') start_date,TO_DATE('2010-05-31','yyyy-mm-dd') end_date FROM DUAL UNION ALL

4 SELECT 3 seq,'AA' tname,TO_DATE('2010-06-11','yyyy-mm-dd') start_date,TO_DATE('2010-08-31','yyyy-mm-dd') end_date FROM DUAL UNION ALL

5 SELECT 4 seq,'AA' tname,TO_DATE('2011-01-12','yyyy-mm-dd') start_date,TO_DATE('2011-06-07','yyyy-mm-dd') end_date FROM DUAL UNION ALL

6 SELECT 5 seq,'BB' tname,TO_DATE('2009-01-01','yyyy-mm-dd') start_date,TO_DATE('2009-05-08','yyyy-mm-dd') end_date FROM DUAL UNION ALL

7 SELECT 6 seq,'BB' tname,TO_DATE('2010-01-01','yyyy-mm-dd') start_date,TO_DATE('2010-12-08','yyyy-mm-dd') end_date FROM DUAL UNION ALL

8 SELECT 7 seq,'BB' tname,TO_DATE('2011-02-01','yyyy-mm-dd') start_date,TO_DATE('2012-01-08','yyyy-mm-dd') end_date FROM DUAL UNION ALL

9 SELECT 8 seq,'BB' tname,TO_DATE('2014-01-01','yyyy-mm-dd') start_date,TO_DATE('2014-11-08','yyyy-mm-dd') end_date FROM DUAL

10 )

11 SELECT m.tname,

12 MIN(m.start_date) start_date,

13 MAX(m.end_date) end_date

14 FROM (SELECT t.*,

15 LAG(t.end_date, 1) OVER(PARTITION BY t.tname ORDER BY t.start_date) last_end_date

16 FROM t) m

17 GROUP BY m.tname,

18 NVL(MONTHS_BETWEEN(TO_DATE(TO_CHAR(m.start_date, 'yyyymm') || '01'), DECODE(last_end_date, NULL, NULL, TO_DATE(TO_CHAR(last_end_date, 'yyyymm') || '01'))), 1)

19 HAVING MONTHS_BETWEEN(TO_DATE(TO_CHAR(MAX(m.end_date),'yyyymm') || '01'),TO_DATE(TO_CHAR(MIN(m.start_date),'yyyymm') || '01')) >= 11

20 ORDER BY 1,2

21 ;

TNAME START_DATE END_DATE

----- ----------- -----------

AA 2009/01/01 2010/08/31

BB 2010/01/01 2010/12/08

BB 2011/02/01 2012/01/08