关于minus 求助!

Question

假设有2张表A,B
A表有
NAME NO
DC   1
DW   2
DF   3
B表有
NAME NO
DC   1
DZ   6
DS   7我要找出A表中没有的完整数据
 select B_NAME_NAME from B  minus select A_NAME from A ;
这样查出来的是
NAME
DZ
DS
但是我想查出的是B表在A表中没有的全部数据
请问如何写?

Answer 1

是这样子的吗？SQL> create table a(name nvarchar2(30),no int);Table createdSQL> create table b(name nvarchar2(30),no int);Table createdSQL> 
SQL> insert into a
  2  select 'dc',1 from dual union select 'dw',2 from dual union select 'df',3 from dual;3 rows insertedSQL> insert into b
  2  select 'dc',1 from dual union select 'dz',6 from dual union select 'ds',7 from dual;3 rows insertedSQL> select * from a;NAME                                                                                              NO
------------------------------------------------------------ ---------------------------------------
dc                                                                                                 1
df                                                                                                 3
dw                                                                                                 2SQL> select * from b;NAME                                                                                              NO
------------------------------------------------------------ ---------------------------------------
dc                                                                                                 1
ds                                                                                                 7
dz                                                                                                 6SQL> select * from b where not exists(select 1 from a where a.name=b.name);NAME                                                                                              NO
------------------------------------------------------------ ---------------------------------------
ds                                                                                                 7
dz                                                                                                 6SQL> 

Answer 2

--LZ用*不就行了
select *
  from (select 'dc' NAME, 1 no
          from dual
        union
        select 'dz', 6
          from dual
        union
        select 'ds', 7 from dual) B
minus
select *
  from (select 'dc', 1
          from dual
        union
        select 'dw', 2
          from dual
        union
        select 'df', 3 from dual) A
--------------------------------------------
    NAME NO
1 ds 7
2 dz 6

Answer 3

我晕，select * from b where not exists(select 1 from a where a.name=b.name);这里面的 1 并不是代表只有1，任何数据都行呀，汗，建议楼主从基础学起

Answer 4

是的.已经想到了
selec * from b 
where  b.b_name  in( select   B_NAME  from   B     minus   select   A_NAME   from   A  )

Answer 5

6楼的就可以了，还可以用外连接实现。select b.* from b left outer join a
on b.name = a.name
where a.name is null;

Answer 6

是的.已经想到了 
selec   *   from   b   
where     b.b_name     in 
( select  B_NAME   from    B    minus    select    A_NAME    from   A  )
------------------
LZ这样简直是多此一举！
既然你要用minus，还不如这样呢：
select * from  B
minus
select * from  A

Answer 7

select   *   from   b   where   not   exists(select   1   from   a   where   a.name=b.name); 
的确简单很多.但是我的实际的2张表的结构还有细微的差别.所以我用IN了,感谢几位朋友