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 部品構成表(
親品番 text,
子品番 text,
所要数 integer);

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

SQL

--■■■階層を上からたどる方法■■■
with recursive rec(親品番,子品番,所要数) as(
select 親品番,子品番,所要数
  from 部品構成表
 where 親品番 = '自転車'
union all
select b.親品番,b.子品番,a.所要数*b.所要数
  from rec a,部品構成表 b
 where a.子品番 = b.親品番)
select 子品番 as 品番,sum(所要数) as 総所要数
  from rec
group by 子品番
order by 総所要数,子品番;

--■■■階層を逆にたどる方法■■■
with recursive rec as(
select 親品番,子品番,所要数,1 as LV,親品番 as root親品番,子品番 as root子品番
  from 部品構成表
union all
select b.親品番,b.子品番,a.所要数*b.所要数,a.LV+1,a.root親品番,a.root子品番
  from rec a,部品構成表 b
 where a.親品番 = b.子品番)
select root子品番 as 品番,sum(所要数) as 総所要数
  from (select 所要数,LV,root子品番,
        max(LV) over(partition by root親品番,root子品番) as MaxLV
        from rec) a
 where LV = MaxLV
group by root子品番
order by 総所要数,品番;

解説

根が事前に分かる場合は、階層を上からたどる方法がシンプルでオススメです。

---