--得到结果:
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(a.bid as varchar)
+case a.bid when b.eid then '' else '--'+cast(b.eid as varchar) end
from #tb1 a,#tb2 b
where a.id=b.id
select isnull(a-b,0) as id_num from
(select 字段 a, ranka = (select count(*) from 表 AA where AA.字段<A.字段)
from 表 A) C
left outer join
(select 字段 b, rankb = (select count(*) from 表 BB where BB.字段<B.字段)
from 表 B) D
on C.ranka = D.rankb+1
--创建数据测试环境
declare @tb table(ID int)
insert into @tb
select 25003
union all select 25004
union all select 25007
union all select 25010
union all select 25015
--得到每个连号的开始编号
select id=identity(int,1,1),cast(id as int) as bid
into #tb1 from @tb a
where not exists(select 1 from @tb where id=a.id-1)
--得到每个连号的结束编号
select id=identity(int,1,1),cast(id as int) as eid
into #tb2 from @tb a
where not exists(select 1 from @tb where id=a.id+1)
--显示结果
select a.bid as 连号开始编号,b.eid as 连号结束编号
from #tb1 a,#tb2 b
where a.id=b.id