导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

关于数字的一个问题

xcj314 2007-12-26 11:32:25
某表A有一字段num,值为:
1
2
4
6
8
9
11
13
55
88
…………
怎么能查出该字段在[1,2,3,4,5,6,7,8,9,10,11…………99]这个区间内,没有出现过的值
...全文
45 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
qiule 2007-12-28
mark
回复
JL99000 2007-12-28
easy
回复
中国风 2007-12-27

--生成结果集再用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 行)

回复
hui_hui_2007 2007-12-27
重发如下:

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)



回复
hui_hui_2007 2007-12-27
看错了,不好意思。
回复
hui_hui_2007 2007-12-27

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 行)
*/
回复
中国风 2007-12-27
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)
回复
中国风 2007-12-26
[1,2,3,4,5,6,7,8,9,10,11…………99]--楼主传的是字符串时,需要一个函数拆分(2000)
回复
dawugui 2007-12-26
我不知道你到底是哪个和哪个比.如果按你最先的要求,是没有结果的.

select * from A where num not in (select num from tmp)
回复
dawugui 2007-12-26
如果数据量大的话,使用一个临时表。

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 2007-12-26
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 2007-12-26
not in
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告