Sum within a self-contained parent-child table

출처 : http://forums.oracle.com/forums/thread.jspa?threadID=828118&tstart=195

SQL> with key_tbl as (
                   select '0' key,null parent_key from dual union all
                   select '1','0' from dual union all
                   select '2','0' from dual union all
                   select '3','0' from dual union all
                   select '1A','1' from dual union all
                   select '2A','2' from dual union all
                   select '2B','2' from dual union all
                   select '3A','3' from dual union all
                   select '3B','3' from dual union all
                   select '3C','3' from dual union all
                   select '1A1','1A' from dual union all
                   select '1A2','1A' from dual union all
                   select '2A1','2A' from dual union all
                   select '2A2','2A' from dual union all
                   select '2B1','2B' from dual union all
                   select '3A1','3A' from dual union all
                   select '3A2','3A' from dual union all
                   select '3C1','3C' from dual union all
                   select '3C2','3C' from dual
                  ),
    amount_tbl as (
                   select '1A1' key,1 amount from dual union all
                   select '1A2',2 from dual union all
                   select '2A1',3 from dual union all
                   select '2A2',4 from dual union all
                   select '2B1',5 from dual union all
                   select '3A1',6 from dual union all
                   select '3A2',7 from dual union all
                   select '3C1',8 from dual union all
                   select '3C2',9 from dual
                  )
  select  key,
          nvl(sum(amount),0) amount
    from  (
           select  connect_by_root k.key key,
                   amount
             from  key_tbl k,
                   amount_tbl a
             where a.key(+) = k.key
             connect by k.parent_key = prior k.key
          )
    group by key
    order by key
  / 

KEY     AMOUNT
--- ----------
0           45
1            3
1A           3
1A1          1
1A2          2
2           12
2A           7
2A1          3
2A2          4
2B           5
2B1          5 

KEY     AMOUNT
--- ----------
3           30
3A          13
3A1          6
3A2          7
3B           0
3C          17
3C1          8
3C2          9 

19 rows selected.