oracle 求取连续日期,求连续项目的开始日期和结束日期

SQL> select * from v;

PROJ_ID PROJ_START PROJ_END

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

1 2005-01-01 2005-01-02

2 2005-01-02 2005-01-03

3 2005-01-03 2005-01-04

4 2005-01-04 2005-01-05

5 2005-01-06 2005-01-07

6 2005-01-16 2005-01-17

7 2005-01-17 2005-01-18

8 2005-01-18 2005-01-19

9 2005-01-19 2005-01-20

10 2005-01-21 2005-01-22

11 2005-01-26 2005-01-27

12 2005-01-27 2005-01-28

13 2005-01-28 2005-01-29

14 2005-01-29 2005-01-30

14 rows selected.

每个项目的开始日期等于前一个项目的结束日期则为连续时间的项目，要求求出连续时间项目开始日期和结束日期SQL> with x as

2 (select proj_id,

3 proj_start,

4 proj_end,

5 case when (lag(proj_end) over (order by proj_id))=proj_start then 0 else 1 end gru

6 from v),

7 y as

8 (select proj_id,

9 proj_start,

10 proj_end,

11 sum(gru)over(order by proj_id) gru /*分组依据*/

12 from x)

13 select min(proj_id) || ' --> ' || max(proj_id) seq_proj,

14 min(proj_start) start_date,

15 max(proj_end) end_date

16 from y

17 group by gru

18 /

SEQ_PROJ START_DATE END_DATE

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

1 --> 4 2005-01-01 2005-01-05

5 --> 5 2005-01-06 2005-01-07

10 --> 10 2005-01-21 2005-01-22

11 --> 14 2005-01-26 2005-01-30

6 --> 9 2005-01-16 2005-01-20