向大家请教一个问题：
有如下数据：
FPHM start_num end_num
---------- ----------- -----------
2013 120 122
2013 124 125
2013 125 128
2014 7 9

需要查询出来连续号段，根据上面的数据应该得出：
2013 124

---网友:grubbyoo

create table test (fphm number ,start_num number,end_num number )

insert into test values(2013,120,122)
insert into test values(2013,124,125)
insert into test values(2013,125,128)
insert into test values(2013,128,129)
insert into test values(2013,130,131) --再增加2个连续
insert into test values(2013,131,144)
insert into test values(2014,7,12)

commit

select fphm,min(root) start_num,end_num
from
(select fphm,end_num,
connect_by_root start_num root
from test a
where connect_by_isleaf=1
connect by start_Num=prior end_num
and fphm= fphm )
group by fphm,end_num
having count(1)<>1
order by 1


结果

fphm start_num end_num
2013 124 129
2013 130 144

my reply:

select distinct aa.fphm,aa.c,max(end_num) from (
select a.fphm,nvl(b.start_num,a.start_num) c,a.end_Num from test A left outer join test B on a.fphm||a.start_num=b.fphm||b.end_num ) AA
group by aa.c,aa.fphm

result:

1 2014 7 12
2 2013 120 122
3 2013 124 128
4 2013 130 144