select * into #temp1 from Table
delect * from Table where 0006<id and id<5555
select min(Id) from #Temp1 where id not in (select * from Table)
delect * from Table where 0006<id and id<5555
select min(Id) from #Temp1 where id not in (select * from Table)
首先你建一个自增列IDENTITY(INT,0,1) as xuhao
然后select top 1 * from #temp1
where xuhao<>id
order by xuhao
where xuhao<>id
order by xuhao
from t
where long(id)+1 not in (select id from t)
declare @t table (id char(3))
insert @t values('001')
insert @t values('002')
insert @t values('003')
insert @t values('007')
insert @t values('009')
select convert(int,ID)+1
from @t
where convert(int,id)+1 not in (select convert(int,id) from @t)
/*-----------
4
8
10(所影响的行数为 3 行)*/
declare @T table(id int)
insert into @t
select 1
union all select 2
union all select 3
union all select 4000
union all select 9007
select A.id+1 as 断号开始,B.id-1 as 断号结束,B.id-A.id-1 as 断号间隔
from @t A,@t B
where A.id<B.id
and not exists (select 1 from @t where id>A.id and id<B.id)
and not exists(select 1 from @t where id=A.id+1)
and not exists(select 1 from @t where id=B.id-1)
select A.id+T1.ID from
(
select A.id*1000+B.id*100+C.id*10+D.id as id from
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) A,
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) B,
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) C,
(select 0 as id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) D
) A,@t T1,@t T2
where T1.id<T2.id
and not exists(select 1 from @t where id>T1.id and ID<t2.id)
and A.id>=1 and A.id+t1.ID<T2.id
ORDER BY A.id+T1.ID
select * from datas where num=a.num+1) and exists(select * from datas
where num>a.num) order by num不要忘了在num上建立“唯一性”索引。