--生成测试数据 create table w_test(w_num number);insert into w_test values(1); insert into w_test values(2); insert into w_test values(3); insert into w_test values(4); insert into w_test values(6); insert into w_test values(7); insert into w_test values(9); insert into w_test values(11); insert into w_test values(12); insert into w_test values(13);--开始查询 select a.w_num||'-'||b.w_num from ( select row_number() over(order by w_num) rn,t.* from w_test t where not exists(select 1 from w_test where w_num=t.w_num-1) ) a join ( select row_number() over(order by w_num) rn,t.* from w_test t where not exists(select 1 from w_test where w_num=t.w_num+1) ) b on (a.rn=b.rn);/* 1-4 6-7 9-9 11-13 */--结束查询 drop table w_test;
写了一个,试试,我自己测试通过了 with tb1 as (select 1 id from dual union all select 2 from dual union all select 3 from dual union all select 4 from dual union all select 6 from dual union all select 7 from dual union all select 9 from dual union all select 11 from dual union all select 12 from dual union all select 13 from dual ) select root_id||'-'||id from( select t2.id id , connect_by_isleaf isleaf, connect_by_root t2.id root_id from tb1 t1 inner join tb1 t2 on t2.id=t1.id start with t2.id in (select id from tb1 where (id-1) not in (select id from tb1)) connect by prior (t2.id+1)=t1.id )where isleaf=1
--生成测试数据
create table w_test(w_num number);insert into w_test values(1);
insert into w_test values(2);
insert into w_test values(3);
insert into w_test values(4);
insert into w_test values(6);
insert into w_test values(7);
insert into w_test values(9);
insert into w_test values(11);
insert into w_test values(12);
insert into w_test values(13);--开始查询
select a.w_num||'-'||b.w_num from (
select row_number() over(order by w_num) rn,t.*
from w_test t where not exists(select 1 from w_test where w_num=t.w_num-1)
) a join (
select row_number() over(order by w_num) rn,t.*
from w_test t where not exists(select 1 from w_test where w_num=t.w_num+1)
) b on (a.rn=b.rn);/*
1-4
6-7
9-9
11-13
*/--结束查询
drop table w_test;
with tb1 as
(select 1 id from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 6 from dual union all
select 7 from dual union all
select 9 from dual union all
select 11 from dual union all
select 12 from dual union all
select 13 from dual )
select root_id||'-'||id from(
select
t2.id id ,
connect_by_isleaf isleaf,
connect_by_root t2.id root_id
from tb1 t1 inner join tb1 t2 on t2.id=t1.id
start with t2.id in (select id from tb1 where (id-1) not in (select id from tb1))
connect by prior (t2.id+1)=t1.id
)where isleaf=1