-- ========================================= -- -----------t_mac 小编------------------- --------------------希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb( id int) go insert tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 11 go select ID+1 from tb where id+1 not in(select id from tb ) and id+1<>(select MAX(id) from tb )+1 go----------- 4 7 10
select top 500 ID=identity(int,1,1)into # from sysobjects,syscolumns select id from tb where id not in(select id from #) drop table #
select * from Tb where id not in (select number from [master].[dbo].[spt_values] where number between 1 and 500)
增加一个自增列 用not in 判断select top 500 ID=identity(int,1,1)into # from sysobjects,syscolumns select id from tb where id not in(select id from #) drop table #
写反了 select distinct number from [master].[dbo].[spt_values] where number between 1 and 500 and number not in (select id from Tb)
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb( id int) go insert tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 11 SELECT DISTINCT NUMBER INTO #T FROM MASTER..SPT_VALUES WHERE NUMBER BETWEEN 1 AND 500 UNION SELECT DISTINCT NUMBER*2 FROM MASTER..SPT_VALUES WHERE NUMBER BETWEEN 1 AND 500SELECT NUMBER FROM #T WHERE NUMBER NOT IN (SELECT ID FROM TB) AND NUMBER<(SELECT MAX(ID) FROM TB) NUMBER ----------- 4 7 10(所影响的行数为 3 行)
--如的如果兩個數連續斷掉,顯然不符合 IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb( id int) go insert tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 --UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 11 go select ID+1 from tb where id+1 not in(select id from tb ) and id+1<>(select MAX(id) from tb )+1 go /* ----------- 4 7 10 */
select top 500 ID=identity(int,1,1) into # from sysobjects,syscolumnsselect id from # where id not in(select id1 from tab) 这样才是对的吧
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int)
go
insert tb SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 11
go
select ID+1
from tb
where id+1 not in(select id from tb ) and
id+1<>(select MAX(id) from tb )+1
go-----------
4
7
10
select top 500 ID=identity(int,1,1)into # from sysobjects,syscolumns
select id from tb where id not in(select id from #)
drop table #
select * from Tb
where id not in (select number from [master].[dbo].[spt_values] where number between 1 and 500)
select id from tb where id not in(select id from #)
drop table #
写反了
select distinct number from [master].[dbo].[spt_values] where number between 1 and 500 and number not in (select id from Tb)
DROP TABLE tb
GO
CREATE TABLE tb( id int)
go
insert tb SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 11
SELECT DISTINCT NUMBER INTO #T FROM MASTER..SPT_VALUES WHERE NUMBER BETWEEN 1 AND 500
UNION
SELECT DISTINCT NUMBER*2 FROM MASTER..SPT_VALUES WHERE NUMBER BETWEEN 1 AND 500SELECT NUMBER FROM #T WHERE NUMBER NOT IN (SELECT ID FROM TB) AND NUMBER<(SELECT MAX(ID) FROM TB)
NUMBER
-----------
4
7
10(所影响的行数为 3 行)
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int)
go
insert tb SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
--UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 11
go
select ID+1
from tb
where id+1 not in(select id from tb ) and
id+1<>(select MAX(id) from tb )+1
go
/*
-----------
4
7
10
*/
select top 500 ID=identity(int,1,1) into #
from sysobjects,syscolumnsselect id
from #
where id not in(select id1 from tab) 这样才是对的吧