一个表的数据怎么排除掉在另外一个表里的数据
1
悬赏园豆：10
[已解决问题]
解决于 2012-03-05 08:57
一个表Test，有A,B,C 三个字段，
另外一个表Test1，有A，B 两个字段
现在要从Test 表中选择出不在Test1 中的数据，应该怎么做？
最佳答案
2
use tempdb
if object_id('Test') is not null drop table Test
if object_id('Test1') is not null drop table Test1
Go
create table Test(
A int,
B int,
C int
)
Go
create table Test1(
A int,
B int,
)
GO
insert into test
select 1,2,3
union all
select 3,4,5
union all
select 6,7,8
GO
insert into test1
select 1,2
union all
select 3,4
union all
select 99,100
Go
select A.* from test A
left join test1 B on A.A=B.A and A.B=B.B
where B.A is null
收获园豆：2
其他回答(7)
0
select * from Test T
where A+B not in (select A+B from Test1)
0
select t1.*from test t1 ,test2 t2 where t1.a!=t2.a or t1.b!=t2.b
收获园豆：2
0
select * from test1 t1 where not exists (select A from test2 where A=t1.A and B=t1.B)
收获园豆：2
0
select * from TEST T
where T.A not in (select A from #TEST1)
OR
T.B not in (select B from #TEST1)
收获园豆：2
0
select t1.*from test t1 ,test2 t2 where t1.a!=t2.a and t1.b!=t2.b
收获园豆：2
0
select * from test t where not exists (select A from test1 where A=t.A and B=t.B)
0
Oracle Minus关键字
SQL中的MINUS关键字
SQL中有一个MINUS关键字，它运用在两个SQL语句上，它先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃，其语法如下：
[SQL Segment 1]
MINUS
[SQL Segment 2]
--------------------------------------------
//创建表1 create table test1 ( name varchar(10), sex varchar(10), );
insert into test1 values('test','female'); insert into test1 values('test1','female'); insert into test1 values('test1','female'); insert into test1 values('test11','female'); insert into test1 values('test111','female');
//创建表2 create table test2 ( name varchar(10), sex varchar(10), );
insert into test2 values('test','female');
insert into test2 values('test2','female');
insert into test2 values('test2','female');
insert into test2 values('test22','female');
insert into test2 values('test222','female');
-------------------------------------------
select * from test1 minus select * from test2;
结果：
NAME SEX
---------- ----------
test1 female
test11 female
test111 female
-----------------------------------------------------------
select * from test2 minus select * from test1;
结果:
NAME SEX
---------- ----------
test2 female
test22 female
test222 female
结论：Minus返回的总是左边表中的数据，它返回的是差集。注意：minus有剃重作用
