关于数字的一个问题

[xcj314]

某表A有一字段num，值为:
1
2
4
6
8
9
11
13
55
88
…………
怎么能查出该字段在[1，2，3，4，5，6，7，8，9，10，11…………99]这个区间内，没有出现过的值

[dawugui]

not in

[dawugui]

create table tb(num int)
insert into tb values(1)
insert into tb values(3)
insert into tb values(5)
insert into tb values(7)
insert into tb values(9)
go

select * from
(
  select 1 as num union select 2 union select 3 union select 4 union select 5 union 
  select 6 union select 7 union select 8 union select 9 union select 10

) t where num not in (select num from tb)

drop table tb

/*
num
-----------
2
4
6
8
10

(5 行受影响)
*/

[dawugui]

如果数据量大的话，使用一个临时表。

SELECT TOP 99 num = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b   

select * from tmp where num not in (select num from A)

drop table A

[dawugui]

我不知道你到底是哪个和哪个比.如果按你最先的要求,是没有结果的.

select * from A where num not in (select num from tmp)

[中国风]

[1，2，3，4，5，6，7，8，9，10，11…………99]--楼主传的是字符串时，需要一个函数拆分(2000)

[中国风]

create function F_test(@s nvarchar(1000))
returns @T table(Col int)
as
begin
while charindex(',',@s)>0
begin
insert @T select left(@s,charindex(',',@s)-1)
set @s=stuff(@s,1,charindex(',',@s),'')
end
insert @T select @s---漏了一句
return
end
go

declare @s nvarchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10,11,99'
select * from F_test(@s)

[hui_hui_2007]

declare @tb table (i int)
declare @tmp table (j int)
declare @i int

insert into @tb
select 23
union select 999
union select 56
union select 888
union select 666

set @i=1
while @i<=99
begin
	insert into @tmp
	select @i
	set @i=@i+1
end

select *
from @tb
/*
i           
----------- 
         23 
         56 
        666 
        888 
        999 

（所影响的行数为 5 行）
*/

select *
from @tb
where i not in (select j from @tmp)
/*
i           
----------- 
        666 
        888 
        999 

（所影响的行数为 3 行）
*/

[hui_hui_2007]

看错了，不好意思。

[hui_hui_2007]

重发如下：

declare @tb table (i int)
declare @tmp table (j int)
declare @i int

insert into @tb
select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9



set @i=1
while @i<=99
begin
	insert into @tmp
	select @i
	set @i=@i+1
end


select *
from @tmp
where j not in (select i from @tb)

[中国风]

--生成结果集再用not in或not exists
go
create function F_test(@s nvarchar(1000))
returns @T table(Col int)
as
begin
while charindex(',',@s)>0
begin
	insert @T select left(@s,charindex(',',@s)-1)
	set @s=stuff(@s,1,charindex(',',@s),'')
end
return 
end
go

declare @s nvarchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10,11,99'
select * from F_test(@s)



Col         
----------- 
1
2
3
4
5
6
7
8
9
10
11

（所影响的行数为 11 行）

[JL99000]

easy

[qiule]

mark