select number from master..spt_values a where number not IN (3,4) and number < 9 and type = 'P'
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-21 17:23:18 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] int) insert [tb] select 3 union all select 4 --------------开始查询-------------------------- declare @minValue int declare @maxValue int declare @uncount varchar(2000) set @minValue = 1--查询的最小值 set @maxValue = 9--查询的最大值 set @uncount = '' --循环查找,如果不存在,则记录编号while (@minValue <= @maxValue) begin if not exists(select * from tb where col = @minValue) begin set @uncount = @uncount + cast(@minValue as varchar) +',' end set @minValue = @minValue + 1 end if(len(@uncount)>0) begin set @uncount = substring(@uncount,1,len(@uncount)-1) end select @uncount as uncount ----------------结果---------------------------- /* uncount ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,2,5,6,7,8,9(1 行受影响) */
你 select number from master..spt_values where type = 'p'就知道原因 了
拿SYSOBJECTS表自己JOIN几下得了
上面number的值最大只能为2047 所以只有2000多
好说SELECT IDD=IDENTITY(INT,1,1),* INTO #T SYSCOLUMNS T,SYSCOLUMNS T1这样应该够了,呵呵,弄死机了别怪我哈,不够再加几次
这个问题关键就是创建一个连续的数字辅助表~ --方法1:带有数字列的系统表 select number from master..spt_values where type = 'p' --方法2:IDENTITY,row_number() over() SELECT number=IDENTITY(INT,1,1),* INTO #T SYSCOLUMNS A,SYSCOLUMNS B --方法3: /* 生产一百万连续数据之临时表 */ SET NOCOUNT ON CREATE TABLE NUM(N INT PRIMARY KEY) INSERT NUM VALUES(1); DECLARE @MAX INT,@RC INT SET @MAX=1000000 SET @RC=1 WHILE @RC*2<=@MAX BEGIN INSERT NUM SELECT N+@RC FROM NUM SET @RC=@RC*2 END INSERT NUM SELECT N+@RC FROM NUM WHERE N+@RC<=@MAX SELECT * FROM NUM go ----------------- /* 函数生产100W连续数据 */ create function dbo.fn_nums(@n as bigint ) returns table as return with L0 as (select 1 as c union all select 1), L1 as (select 1 as c from L0 AS A,L0 AS B), L2 AS (SELECT 1 AS C FROM L1 AS A,L1 AS B), L3 AS (SELECT 1 AS C FROM L2 AS A,L2 AS B), L4 AS (SELECT 1 AS C FROM L3 AS A,L3 AS B), L5 AS (SELECT 1 AS C FROM L4 AS A,L4 AS B), NUMS AS (SELECT ROw_number() over (order by c) as n from L5) select n from nums where n<=@nselect * from dbo.fn_nums(1000000)
from master..spt_values a
where not exists(select 1 from 你的表 where 你的列 = a.number)
and number < 9 and type = 'P'
from master..spt_values a
where number not IN (select COL from 你的表)
and number < 9 and type = 'P'
from master..spt_values a
where number not IN (3,4)
and number < 9 and type = 'P'
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-21 17:23:18
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 3 union all
select 4
--------------开始查询--------------------------
declare @minValue int
declare @maxValue int
declare @uncount varchar(2000)
set @minValue = 1--查询的最小值
set @maxValue = 9--查询的最大值
set @uncount = ''
--循环查找,如果不存在,则记录编号while (@minValue <= @maxValue)
begin
if not exists(select * from tb where col = @minValue)
begin
set @uncount = @uncount + cast(@minValue as varchar) +','
end
set @minValue = @minValue + 1
end
if(len(@uncount)>0)
begin
set @uncount = substring(@uncount,1,len(@uncount)-1)
end
select @uncount as uncount
----------------结果----------------------------
/* uncount
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,5,6,7,8,9(1 行受影响)
*/
你
select number from master..spt_values where type = 'p'就知道原因 了
上面number的值最大只能为2047 所以只有2000多
--方法1:带有数字列的系统表
select number from master..spt_values where type = 'p'
--方法2:IDENTITY,row_number() over()
SELECT number=IDENTITY(INT,1,1),* INTO #T SYSCOLUMNS A,SYSCOLUMNS B
--方法3:
/*
生产一百万连续数据之临时表
*/
SET NOCOUNT ON
CREATE TABLE NUM(N INT PRIMARY KEY)
INSERT NUM VALUES(1);
DECLARE @MAX INT,@RC INT
SET @MAX=1000000
SET @RC=1
WHILE @RC*2<=@MAX
BEGIN
INSERT NUM
SELECT N+@RC FROM NUM
SET @RC=@RC*2
END
INSERT NUM
SELECT N+@RC FROM NUM WHERE N+@RC<=@MAX
SELECT * FROM NUM
go
-----------------
/*
函数生产100W连续数据
*/
create function dbo.fn_nums(@n as bigint )
returns table
as return
with
L0 as (select 1 as c union all select 1),
L1 as (select 1 as c from L0 AS A,L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A,L1 AS B),
L3 AS (SELECT 1 AS C FROM L2 AS A,L2 AS B),
L4 AS (SELECT 1 AS C FROM L3 AS A,L3 AS B),
L5 AS (SELECT 1 AS C FROM L4 AS A,L4 AS B),
NUMS AS (SELECT ROw_number() over (order by c) as n from L5)
select n from nums where n<=@nselect * from dbo.fn_nums(1000000)