10-147 自分と祖先の総合計を求める

SQLパズル

IDTable
ID  PrevID  Val
--  ------  ---
 1    null   10
 2       1   20
 3       1   30
 4       2   15
 5       4   40
 6       4   50
 7       5   60
90    null   10
91      90   20

ID=1の行と、その子孫の行の、
自分と祖先の総合計を求め、
以下の出力をする

出力結果
ID  PrevID  Val  Level  SumVal
--  ------  ---  -----  ------
 1    null   10      1      10
 2       1   20      2      30
 3       1   30      2      40
 4       2   15      3      45
 5       4   40      4      85
 6       4   50      4      95
 7       5   60      5     145

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table IDTable(
ID     number(2),
PrevID number(2),
Val    number(2));

insert into IDTable values( 1,null,10);
insert into IDTable values( 2,   1,20);
insert into IDTable values( 3,   1,30);
insert into IDTable values( 4,   2,15);
insert into IDTable values( 5,   4,40);
insert into IDTable values( 6,   4,50);
insert into IDTable values( 7,   5,60);
insert into IDTable values(90,null,10);
insert into IDTable values(91,  90,20);
commit;

SQL

--■■■connect_by_rootを使う方法(10g以降)■■■
select RootID as ID,
RootPrevID as PrevID,
max(decode(lv,1,Val)) as Val,
max(lv)  as "Level",
sum(Val) as SumVal
  from (select
        connect_by_root ID as RootID,
        connect_by_root PrevID as RootPrevID,
        ID,Val,Level as lv
          from IDTable
        connect by prior PrevID = ID)
group by RootID,RootPrevID
having max(case when ID = 1 then 1 else 0 end) = 1
order by RootID,RootPrevID;

--■■■相関サブクエリを使う方法■■■
select ID,PrevID,Val,Level,
(select sum(b.Val)
  from IDTable b
Start With b.RowID = a.RowID
connect by ID = prior PrevID) as SumVal
  from IDTable a
Start With ID = 1
connect by prior ID = PrevID
order by ID,PrevID;

解説

connect_by_rootを使う方法では、
データ構造が、
親が、最大でも1つなこと(逆に探索しても枝分かれが発生しない)
を使ってます

類似のSQLとして4-12 階層の総積を取得があります