if object_id('tb') is not null drop table tb go create table tb(id int) insert tb select 1 union all select 2 union all select 4 union all select 6 union all select 8 go select number+1 as id from master..spt_values where type='p' and number+1 not in(select id from tb) and number+1<=(select max(id) from tb) /* id ----------- 3 5 7(3 行受影响) */
if object_id('tb') is not null drop table tb go create table tb(id int) insert tb select 1 union all select 2 union all select 4 union all select 6 union all select 8 select top 8 id=identity(int,1,1) into # from syscolumns select id from # where id not in (select id from tb) drop table # /*id ----------- 3 5 7(3 行受影响)*/
用spt_values建一个连续的ID,找出不在表中的ID select number+1 as id from master..spt_values where type='p' and number+1 not in(select id from tb) and number+1<=(select max(id) from tb)
if object_id('tb') is not null drop table tb go create table tb(id int) insert tb select 1 union all select 2 union all select 4 union all select 6 union all select 8select top 8 id=identity(int,1,1) into # from syscolumns select * from # select * from # where id not in(select * from tb)
一模一样不如创新一点。if object_id('tb') is not null drop table tb go create table tb(id int) insert tb select 1 union all select 2 union all select 4 union all select 6 union all select 8 go ;with h as ( select id=row_number() over(order by getdate()) from syscolumns a,syscolumns b ) select id from h where id <(select max(id) from tb) and id not in(select id from tb) /* id ----------- 3 5 7(3 行受影响) */
if object_id('tb') is not null drop table tb go create table tb(id int) insert tb select 1 union all select 2 union all select 4 union all select 6 union all select 8 go ;with h as ( select id=row_number() over(order by getdate()) from syscolumns a,syscolumns b ) select id from tb left join h on tb.id = h.id where h.id is not null
drop table tb
go
create table tb(id int)
insert tb select 1
union all select 2
union all select 4
union all select 6
union all select 8
go
select number+1 as id from master..spt_values where type='p' and number+1 not in(select id from tb) and number+1<=(select max(id) from tb)
/*
id
-----------
3
5
7(3 行受影响)
*/
drop table tb
go
create table tb(id int)
insert tb select 1
union all select 2
union all select 4
union all select 6
union all select 8
select top 8 id=identity(int,1,1) into # from syscolumns
select id from # where id not in (select id from tb)
drop table #
/*id
-----------
3
5
7(3 行受影响)*/
用spt_values建一个连续的ID,找出不在表中的ID
select number+1 as id
from master..spt_values where type='p' and number+1
not in(select id from tb) and number+1<=(select max(id) from tb)
drop table tb
go
create table tb(id int)
insert tb select 1
union all select 2
union all select 4
union all select 6
union all select 8select top 8 id=identity(int,1,1) into # from syscolumns
select * from #
select * from # where id not in(select * from tb)
一模一样不如创新一点。if object_id('tb') is not null
drop table tb
go
create table tb(id int)
insert tb select 1
union all select 2
union all select 4
union all select 6
union all select 8
go
;with h as
(
select id=row_number() over(order by getdate()) from syscolumns a,syscolumns b
)
select id from h where id <(select max(id) from tb) and id not in(select id from tb)
/*
id
-----------
3
5
7(3 行受影响)
*/
drop table tb
go
create table tb(id int)
insert tb select 1
union all select 2
union all select 4
union all select 6
union all select 8
go
;with h as
(
select id=row_number() over(order by getdate()) from syscolumns a,syscolumns b
)
select id from tb left join h on tb.id = h.id where h.id is not null