外连接与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;