oracle查询连续间隔段

create table tx(v1 varchar2(20), v2 varchar2(20));

insert into tx
select '2014','00000001' from dual union all
select '2014','00000002' from dual union all
select '2014','00000003' from dual union all
select '2014','00000004' from dual union all
select '2014','00000005' from dual union all
select '2014','00000007' from dual union all
select '2014','00000008' from dual union all
select '2014','00000009' from dual union all
select '2013','00000120' from dual union all
select '2013','00000121' from dual union all
select '2013','00000122' from dual union all
select '2013','00000124' from dual union all
select '2013','00000125' from dual

select * from tx

分析函数方法,仅限oracle

with temp as (
select v1, v2, to_number(v2) num1 ,lag(to_number(v2)) over(partition by v1 order by to_number(v2)) num2 from tx
)
, temp2 as(
select v1, v2, num1, num2, case when  num1-num2 = 1 then 0 else 1 end ty  from temp
)
,temp3 as (
select v1, v2, num1, num2,sum(ty) over(order by to_number(v1),to_number(v2)) ty2 from temp2
)
select v1,min(v2), max(v2) from temp3 group by v1, ty2 order by v1

not exists，可用于其它数据库，该方法从网上发现找，整理起来。

select res1.v1, res2.v2 str, res1.v2  from (
select rownum rn, c.v1, c.v2 from (select * from tx a where not exists( select null from  tx b where a.v1=b.v1 and to_number(a.v2) = to_number(b.v2) - 1) order by v1, to_number(v2) ) c )res1,
(
select rownum rn, c.v1, c.v2 from (select * from tx a where not exists( select null from  tx b where a.v1=b.v1 and to_number(a.v2) = to_number(b.v2) + 1) order by v1, to_number(v2) ) c ) res2
where res1.v1 = res2.v1
  and res1.rn = res2.rn