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
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 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