7-46 階層の子と子孫の数を取得

SQLパズル

社員テーブル
社員ID     役職  上司
------  -------  ----
     1  部長A    null
    11  課長A       1
    21  係長A      11
    31  平社員A    21
    12  課長B       1
    22  係長B      12
    32  平社員B    22

社員テーブルから以下の出力を行う。

出力結果
階層レベル  社員ID      役職    上司   部下数  直属部下数
----------  ------  --------  -----   -----  ----------
         1       1    部長A    null       6           2
         2      11    課長A       1       2           1
         3      21    係長A      11       1           1
         4      31  平社員A      21       0           0
         2      12    課長B       1       2           1
         3      22    係長B      12       1           1
         4      32  平社員B      22       0           0

データ作成スクリプト

create table 社員(
社員ID number(2),
役職   varchar2(7),
上司   number(2));

insert into 社員 values( 1,'部長A'  ,null);
insert into 社員 values(11,'課長A'  ,   1);
insert into 社員 values(21,'係長A'  ,  11);
insert into 社員 values(31,'平社員A',  21);
insert into 社員 values(12,'課長B'  ,   1);
insert into 社員 values(22,'係長B'  ,  12);
insert into 社員 values(32,'平社員B',  22);
commit;

SQL

select Level as 階層レベル,
社員ID,役職,上司,
(select count(*) from 社員 b where Level >= 2
 start with b.RowID=a.RowID
 connect by prior 社員ID = 上司) as 部下数,
(select count(*) from 社員 b
  where b.上司=a.社員ID) as 直属部下数
from 社員 a
start with 社員ID = 1
connect by PRIOR 社員ID = 上司;

解説

RowIDをstart withの条件として、
階層問い合わせをして、階層の子孫の数を取得してます。

---