SQL查询连续号码段

SQL查询连续号码段

--测试数据

create table stest
(
 fphm int,
 kshm nvarchar(10)
)

insert into stest
select 2014,'00000001'
union all
select 2014,'00000002'
union all
select 2014,'00000003'
union all
select 2014,'00000004'
union all
select 2014,'00000005'
union all
select 2014,'00000007'
union all
select 2014,'00000008'
union all
select 2014,'00000009'
union all
select 2013,'00000120'
union all
select 2013,'00000121'
union all
select 2013,'00000122'
union all
select 2013,'00000124'
union all
select 2013,'00000125'
--执行语句
select fphm,min(kshm),max(kshm)
from
(
select fphm,kshm,cast(kshm as int)-cc as s
from
(
select fphm,kshm,row_number() over(order by fphm) as cc
from stest
) xx
) b group by b.fphm,b.s