declare @a table (id int) insert @a values(1) insert @a values(2) insert @a values(5) insert @a values(7) select c.id+1 from @a c where id not in (select a.id from @a a ,@a b where a.id=b.id-1) and c.id not in(select max(id) from @a)
create table ITtable(id int) insert ITtable select 1 union all select 2 union all select 5 union all select 7select id+1 from ITtable T where not exists(select 1 from ITtable where id=T.id+1)drop table ITtable/* ----------- 3 6 8(3 row(s) affected) */
如果不要8: create table ITtable(id int) insert ITtable select 1 union all select 2 union all select 5 union all select 7select id+1 from ITtable T where id<(select max(id) from ITtable) and not exists(select 1 from ITtable where id=T.id+1)drop table ITtable/* ----------- 3 6(3 row(s) affected) */
declare @a table (id int)
insert @a values(1)
insert @a values(2)
insert @a values(5)
insert @a values(7)
select c.id+1 from @a c where id not in (select a.id from @a a ,@a b where a.id=b.id-1) and c.id not in(select max(id) from @a)
insert ITtable select 1
union all select 2
union all select 5
union all select 7select id+1 from ITtable T
where not exists(select 1 from ITtable where id=T.id+1)drop table ITtable/*
-----------
3
6
8(3 row(s) affected)
*/
create table ITtable(id int)
insert ITtable select 1
union all select 2
union all select 5
union all select 7select id+1 from ITtable T
where id<(select max(id) from ITtable) and not exists(select 1 from ITtable where id=T.id+1)drop table ITtable/*
-----------
3
6(3 row(s) affected)
*/