SQL> create table hierarchy (child,parent)

  2  as

  3  select 1, null from dual union all

  4  select 2, 1 from dual union all

  5  select 3, 1 from dual union all

  6  select 4, 2 from dual union all

  7  select 5, 2 from dual

  8  /



Tabel is aangemaakt.



SQL> create table data (hierarchyid,value)

  2  as

  3  select 3, 10 from dual union all

  4  select 4, 20 from dual union all

  5  select 5, 30 from dual

  6  /



Tabel is aangemaakt.



SQL>  select h.child

  2        , sum(connect_by_root d.value)

  3     from hierarchy h

  4        , data d

  5    where h.child = d.hierarchyid 

  6  connect by prior h.parent = h.child

  7    group by h.child

  8    order by h.child

  9  /



     CHILD SUM(CONNECT_BY_ROOTD.VALUE)


---------------------------

         1                          60

         2                          50

         3                          10

         4                          20

         5                          30



5 rijen zijn geselecteerd.

with 

the_data as

(select 1 sub,null sup,0 amount from dual union all

 select 2 sub,1 sup,0 from dual union all

 select 3 sub,1 sup,0 from dual union all

 select 4 sub,2 sup,0 from dual union all

 select 4 sub,3 sup,0 from dual union all 

 select 5 sub,4 sup,10 from dual union all 

 select 6 sub,4 sup,25 from dual

)

select distinct 

       sub,

       case when connect_by_isleaf = 0

            then (select sum(amount)

                    from the_data

                   start with sup = d.sub

                   connect by prior sub = sup                 

                 ) + amount

            else amount

       end level_sum

  from the_data d 

 start with sup is null 

 connect by prior sub = sup

 order by sub

...

=============

SUB|LEVEL_SUM

=============

  1|       70

  2|       35

  3|       35

  4|       35

  5|       10

  6|       25