可用分押宝游戏火热进行中...	专题改版：Java Web 专题

判断ID"1,2,3,4,5"中的哪些在表a中不存在!!!

楼主yxandyx（若有所思）2006-03-03 15:44:00 在 MS-SQL Server / 基础类提问

也就是找出id1,2,3,4,5在表a中不存在的!比如1,2在表a中不存在,那么就显示出来!! 问题点数：20、回复次数：12Top

1 楼yxandyx（若有所思）回复于 2006-03-03 15:49:19 得分 0

难度很大呀Top

2 楼xeqtr1982（Visual C# .NET）回复于 2006-03-03 15:51:59 得分 0

id有规律吗？Top

3 楼zhaoanle（zhao）回复于 2006-03-03 15:54:07 得分 5

select top 5 identity(int,1,1) as id into #tbl from sysobjects,syscolumns
select id from #tbl where id not in (select id from a)Top

4 楼yxandyx（若有所思）回复于 2006-03-03 15:56:09 得分 0

id没有规律Top

5 楼yxandyx（若有所思）回复于 2006-03-03 15:57:44 得分 0

zhaoanle(zhao) ( ) 信誉：100 2006-03-03 15:54:00 得分: 0

select top 5 identity(int,1,1) as id into #tbl from sysobjects,syscolumns
select id from #tbl where id not in (select id from a)

==========================
必须用到零时表吗Top

6 楼wgsasd311（自强不息）回复于 2006-03-03 15:57:48 得分 10

select * from (select '1' as id union all
select '2' as id union all
select '3' as id union all
select '4' as id union all
select '5' as id ) a where not exists(select 1 from tb where id=a.id)Top

7 楼yxandyx（若有所思）回复于 2006-03-03 16:04:10 得分 0

wgsasd311(自强不息) ( ) 信誉：100
============
我的ID数量不是固定的,有可能很多呀Top

8 楼yxandyx（若有所思）回复于 2006-03-03 16:04:46 得分 0

比如我有可能是"1,2,3,4,5,6,7,8,9,10,"或者更多,或者更少去判断Top

9 楼mislrb（上班看看早报，上上CSDN，下班看看电影）回复于 2006-03-03 16:05:12 得分 5

select top 5 identity(int,1,1) as id into #t from sysobjects,syscolumns
select t.id from #t t where not exists(select 1 from a where id=t.id)
Top

10 楼zhaoanle（zhao）回复于 2006-03-03 16:08:00 得分 0

--我只想出这个方法了
select top 5 identity(int,1,1) as id into #tbl from sysobjects,syscolumns
select id from #tbl where id not in (select id from a)

--如果ID多的话就加大临时表的行数
select top 10000 identity(int,1,1) as id into #tbl from sysobjects,syscolumns
select id from #tbl where id not in (select id from a)Top

11 楼yxandyx（若有所思）回复于 2006-03-03 16:08:48 得分 0

wgsasd311(自强不息) ( ) 信誉：100
很好用,谢谢Top

12 楼yxandyx（若有所思）回复于 2006-03-03 16:10:13 得分 0

谢谢大家Top