sorry:select top 1 cast(bm as int)+1 from test where bm>101 order by cast(bm as int)
从纪录 [101,102,106] 来近回 [103]一个不存在纪录 的sql可能是不可能的.
TRY: 1,建序数表 select top 8000 identity(int,1,1) as N into numtab from (select top 100 id=1 from sysobjects) as a, (select top 100 id=1 from sysobjects) as b, (select top 100 id=1 from sysobjects) as c 2, select top 1 N from numtab full join test on cast(bm as int)=Numtab.N where test.bm>'101' and test.bm is null order by test.bm
declare @i int set @i = 101 set @i = @i + 1 while exists (select * from test where bm = @i) set @i = @i + 1 select @i
declare @a varchar(10) select @a=101 select @a=case when @a=bm-1 then @a+1 else @a end from test where bm>101 select @a+1
from test
where bm>101
order by cast(bm as int)
1,建序数表
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c
2,
select top 1 N from numtab full join test on cast(bm as int)=Numtab.N
where test.bm>'101' and test.bm is null
order by test.bm
set @i = 101
set @i = @i + 1
while exists (select * from test where bm = @i)
set @i = @i + 1
select @i
select @a=101
select @a=case when @a=bm-1 then @a+1 else @a end
from test where bm>101
select @a+1