7-80 階層問い合わせで部下一覧を作成

SQLパズル

組織テーブル
役職  上司
----  ----
社長  Null
課長  社長
平1   課長
平2   課長

下記の部下一覧を作成する。
部下が1人もいない場合は、部下をnullとして表示する。

出力結果
役職  部下
----  ----
社長  課長
社長  平1
社長  平2
課長  平1
課長  平2
平1   null
平2   null

データ作成スクリプト

create table 組織(役職,上司) as
select '社長',Null   from dual union
select '課長','社長' from dual union
select '平1','課長'  from dual union
select '平2','課長'  from dual;

SQL

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(root役職,役職,上司,LV) as(
select 役職,役職,NULL,1
  from 組織
union all
select a.root役職,b.役職,b.上司,LV+1
  from rec a,組織 b
 where a.役職 = b.上司)
select root役職,nullif(役職,root役職) as 部下
  from (select count(*) over(partition by root役職) as cnt,
        root役職,役職,LV
        from rec)
where cnt = 1 or LV > 1
order by cnt desc,root役職,LV;

--■■■階層問い合わせを使う方法(10g以降)■■■
select root役職,nullif(役職,root役職) as 部下
from (select connect_by_root 役職 as root役職,
      役職,Level as LV
        from 組織
       where connect_by_IsLeaf = 1 or  Level > 1
      connect by prior 役職 = 上司)
order by count(*) over(partition by root役職) desc,root役職,LV;

解説

この場合は、階層問い合わせを使うほうがシンプルですねぇ

---