首页新闻找找看学习计划

一个表的数据怎么排除掉在另外一个表里的数据

1
悬赏园豆:10 [已解决问题] 解决于 2012-03-05 08:57

一个表Test,有A,B,C 三个字段,

另外一个表Test1,有A,B 两个字段

现在要从Test 表中选择出不在Test1 中的数据,应该怎么做?

maanshancss的主页 maanshancss | 菜鸟二级 | 园豆:303
提问于:2012-02-24 10:55
< >
分享
最佳答案
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
小材小用 | 小虾三级 |园豆:639 | 2012-02-28 11:17
其他回答(7)
0

select * from Test  T

where A+B not in (select A+B from Test1)

maanshancss | 园豆:303 (菜鸟二级) | 2012-02-24 11:09
0

select t1.*from test t1 ,test2 t2 where t1.a!=t2.a or t1.b!=t2.b

收获园豆:2
sunlary | 园豆:934 (小虾三级) | 2012-02-24 11:19
0

select * from test1 t1 where not exists (select A from test2 where A=t1.A and B=t1.B)

收获园豆:2
Hex.Miao | 园豆:207 (菜鸟二级) | 2012-02-24 13:35
0

select * from TEST T

where T.A not in (select A from #TEST1)

OR

T.B not in (select B from #TEST1)

收获园豆:2
子夜一梦 | 园豆:213 (菜鸟二级) | 2012-02-24 16:34
0

select t1.*from test t1 ,test2 t2 where t1.a!=t2.a and t1.b!=t2.b

收获园豆:2
每当变幻时 | 园豆:191 (初学一级) | 2012-02-24 20:21
0

select * from test t where not exists (select A from test1 where A=t.A and B=t.B)

丘山走云 | 园豆:205 (菜鸟二级) | 2012-02-28 11:58
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有剃重作用

空杯椰子 | 园豆:219 (菜鸟二级) | 2012-03-14 15:14
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册