外连接与union

 

一道有点意思的oracle试题现转如下：

有两个表，表a有两个字段，分别是部门code和子部门code，表b有两个字段，部门code，和部门name

表sectionCfg 如下：                              表sectionINfo： 

secID     subSecID                         secID          secName 
2                   5                                       2                   部门2  
2                   6                                       3                   部门3
3                   7                                       4                   部门4
3                   8                                       5                   科1
4                   9                                       6                   科2
4                   10                                     7                   科1
                                                            8                   科2
                                                            9                   科1  
                                                            10                 科2

想输出这样的结果：  

secID  secName  subName
2           部门2
5           部门2          科1  
6           部门2          科2  
3           部门3  
7           部门3          科1  
8           部门3           科2  
4           部门4  
9           部门4           科1  
10        部门4           科2 
 

我自己的解决思路：

运用外连接最后实现三列的结果集

表A：secID   部门Name 即结果集的第一列和第二列

表B：secid   subname 即科室的id和name

然后表A表B外连接得到最终结果

难点在于得到表A

把部门和子部门分开考虑利用union合并。两次引用sectionInfo表

select  A.secid,
       A.secname,
       B.SECNAME
from  
        ( select distinct  i.SECID,
             i.SECNAME
        from  sectioncfg c,
                sectioninfo i
        where c.SECID =  i.SECID
    union  
        select  c.SUBSECID,
       i2.SECNAME
       from  sectioncfg c,
     sectioninfo i,
     sectioninfo i2
       where i.SECID = c.SUBSECID and
      c.SECID  =  i2.SECID
    order by  secname )  A,
        ( select  i.SECID,
    i.SECNAME
           from  sectioncfg c,
     sectioninfo i
           where  i.SECID  =  c.SUBSECID
           order   by  i.SECID)  B
where  A.secid  =  B.secid( + )  
order   by  A.secname,A.secid

另一种类似的思路，但简单

 

select  aa.subsecid,
       b2.secname,
       decode(b1.secname,b2.secname, null ,b1.secname)  as  subdep
   from  (
         select  c.SUBSECID,
               c.SECID
           from  sectioncfg c
         union   all
         select   distinct
               c.SECID,
               c.SECID
           from  sectioncfg c
           order   by  secid
       )aa,
       sectioninfo b1,
       sectioninfo b2
  where  aa.secid  =  b2.secid
    and  aa.subsecid  =  b1.secid
  order   by   2 , 1 ;