4-12 階層の総積を取得

SQLパズル

部品構成表テーブル
親品番    子品番     所要数
--------  ------    ------
自転車    ハンドル       1
自転車    フレーム       1
自転車    車輪           2
自転車    ブレーキ       2
ハンドル  グリップ       2
ハンドル  留め具         4
フレーム  サドル         1
フレーム  ペダル         2
車輪      スポーク      10
ペダル    留め具         3

部品構成表テーブルから品番ごとの、総所要数を出力する

出力結果
品番      総所要数
--------  --------
サドル           1
ハンドル         1
フレーム         1
グリップ         2
ブレーキ         2
ペダル           2
車輪             2
留め具          10  ←1*4+2*3
スポーク        20  ←2*10

データ作成スクリプト

create table 部品構成表(
親品番 varchar2(8),
子品番 varchar2(8),
所要数 number(2));

insert into 部品構成表 values('自転車'  ,'ハンドル', 1);
insert into 部品構成表 values('自転車'  ,'フレーム', 1);
insert into 部品構成表 values('自転車'  ,'車輪'    , 2);
insert into 部品構成表 values('自転車'  ,'ブレーキ', 2);
insert into 部品構成表 values('ハンドル','グリップ', 2);
insert into 部品構成表 values('ハンドル','留め具'  , 4);
insert into 部品構成表 values('フレーム','サドル'  , 1);
insert into 部品構成表 values('フレーム','ペダル'  , 2);
insert into 部品構成表 values('車輪'    ,'スポーク',10);
insert into 部品構成表 values('ペダル'  ,'留め具'  , 3);
commit;

SQL

--■■■階層を逆にたどる方法■■■
select Root子品番 as 品番,sum(総積) as 総所要数
from(select
     Root親品番,Root子品番,
     round(exp(sum(Ln(所要数)))) as 総積
     from (select 親品番,子品番,
           Connect_by_Root 親品番 as Root親品番,
           Connect_by_Root 子品番 as Root子品番,
           所要数
           from 部品構成表
           connect by prior 親品番 = 子品番)
     group by Root親品番,Root子品番)
group by Root子品番
order by 総所要数,Root子品番;

--■■■階層を上からたどる方法■■■
select 子品番,
sum((select exp(sum(Ln(b.所要数)))
       from 部品構成表 b
      where instr(a.path,RowIDToChar(b.RowID)) > 0)) as sumVal
from (select 子品番,
      sys_connect_by_path(RowIDToChar(RowID),',') as path
        from 部品構成表
      start with 親品番 = '自転車'
      connect by prior 子品番 = 親品番) a
group by 子品番
order by sumVal,子品番;

解説

インラインビューで階層を逆にたどって、総積を取得した後、
総合計を取得してます

---