有表如下:
SQL> select * from yu1;
COL1 COL2
---------- --------------------
2014 00000001
2014 00000002
2014 00000003
2014 00000004
2014 00000005
2014 00000009
2014 00000010
2013 00000013
2013 00000014
2013 00000015
2013 00000017
COL1 COL2
---------- --------------------
2013 00000018
2013 00000020
2013 00000021
欲求出下列结果
col1 start end
2014 00000001 00000005
2014 00000009 00000010
2013 00000013 00000015
2013 00000017 00000018
2013 00000020 00000021
select col1, min(col2) "start" , max(col2) "end"
from yu1 a
start with not exists (select 1 from yu1 b where b.col2 = a.col2 - 1)
connect by prior a.col2 = a.col2 - 1
group by rownum - level, col1;