表的连接查询

Question

想请教一下表的连接查询.对于A表中的数据如下:(A表是保存用户的请假初始化值)
   EN_NAME      LV_TYPE      INIT    
------------ ------------ ---------- 
Anco         A                    12 
Anco         B                    15 
Anco         C                     8 
Jerry        A                     8 B表中的数据如下:(B表是用户使用的假期情况)
   EN_NAME      LV_TYPE      USED    
------------ ------------ ---------- 
Anco         A                     2 
Anco         D                     1     --注:对于D假期,虽然没有被初始化,但还是可用.我想通过连接查询得到如下的表述汇总:
   EN_NAME      LV_TYPE                    INIT                     USED    
------------ ------------ -------------------------------------- ---------- 
Anco         A                                                12          2 
Anco         B                                                15                                      
Anco         C                                                 8                                      
Jerry        A                                                 8                                      
Anco         D                                                            1   我的尝试,我尝试用提外连接:select * from A full join B on a.en_name=b.en_name and a.lv_type=b.lv_typeQuery finished, retrieving results...
   EN_NAME      LV_TYPE                    INIT                    EN_NAME_1    LV_TYPE_1     USED    
------------ ------------ -------------------------------------- ------------ ------------ ---------- 
Anco         A                                                12 Anco         A                     2 
Anco         B                                                15                                      
Anco         C                                                 8                                      
Jerry        A                                                 8                                      
                                                                 Anco         D                     1 
对于红色的列是重复的,我只想取一次.

Answer 1

select en_name,lv_type,init,used from A a, B b where a.en_name=b.en_name and a.lv_type=b.lv_type 
试试这个

Answer 2

Processing ...
select a.en_name,a.lv_type,a.init,b.used from A a, B b where a.en_name=b.en_name and a.lv_type=b.lv_typeQuery finished, retrieving results...
   EN_NAME      LV_TYPE      INIT       USED    
------------ ------------ ---------- ---------- 
Anco         A                    12          2 这样只能得到一笔数据,简单的连接是无法实现的.

Answer 3

SELECT a.en_name,a.lv_type,a.init,b.used
  FROM A
  FULL JOIN B ON A.EN_NAME = B.EN_NAME
             AND A.LV_TYPE = B.LV_TYPE;

Answer 4

如果这个方式的话,对于Anco请的假D不就体现的出来了么?
最终我想要的结果是:
  EN_NAME      LV_TYPE                    INIT                    USED    
------------ ------------ -------------------------------------- ---------- 
Anco        A                                                12          2 
Anco        B                                                15       NULL                               
Anco        C                                                 8       NULL                              
Jerry       A                                                 8       NULL                               
Anco        D                                              NULL          1     --注意,Anco请的D假是我最关心的.
问题虽小,也挺磨人滴!呵呵.

Answer 5

SELECT nvl(A.EN_NAME,b.en_name), nvl(A.LV_TYPE,b.lv_type), A.INIT, B.USED
  FROM A
  FULL JOIN B ON A.EN_NAME = B.EN_NAME
             AND A.LV_TYPE = B.LV_TYPE;

Answer 6

Select En_Name,
 Lv_Type,
 (Select Init
From a
 Where a.En_Name = T.En_Name
 And a.Lv_Type = T.Lv_Type) As INIT,
 (Select USED
From b
 Where b.En_Name = T.En_Name
 And b.Lv_Type = T.Lv_Type) As USED
From (Select En_Name, Lv_Type
From a
Union
Select En_Name, Lv_Type From b) T;