oracle 向上汇总相关问题

[早起鸟儿]

with t as
(select 'r1' as 仓库ID, 'r0' as 上级仓库ID, '0001' as 备件编码, '3' as 数量
from dual
union all
select 'r1' as 仓库ID, 'r0' as 上级仓库ID, '0002' as 备件编码, '3' as 数量
from dual
union all
select 'r1' as 仓库ID, 'r0' as 上级仓库ID, '0004' as 备件编码, '3' as 数量
from dual
union all
select 'r2' as 仓库ID, 'r1' as 上级仓库ID, '0005' as 备件编码, '2' as 数量
from dual
union all
select 'r2' as 仓库ID, 'r1' as 上级仓库ID, '0002' as 备件编码, '3' as 数量
from dual
union all
select 'r2' as 仓库ID, 'r1' as 上级仓库ID, '0004' as 备件编码, '3' as 数量
from dual
union all
select 'r3' as 仓库ID, 'r2' as 上级仓库ID, '0006' as 备件编码, '2' as 数量
from dual
union all
select 'r3' as 仓库ID, 'r2' as 上级仓库ID, '0002' as 备件编码, '3' as 数量
from dual
union all
select 'r3' as 仓库ID, 'r2' as 上级仓库ID, '0004' as 备件编码, '3' as 数量
from dual
union all
select 'r3' as 仓库ID, 'r2' as 上级仓库ID, '0004' as 备件编码, '3' as 数量
from dual)
希望得到的结果是 ：
仓库ID 上级仓库ID 备件编码 数量
r1 r0 0001 3
r1 r0 0002 9
r1 r0 0004 12
r1 r0 0005 2
r1 r0 0006 2


这个树有几层是不确定的！

[zbdzjx]

按照你给的数据及结果，直接group by 备件编码，sum(数量)，就出来结果了啊。好像和有几层没关系吧。

[早起鸟儿]

自己顶一下！！！

[早起鸟儿]

自己顶一下！！！

[csliu_cool887]

  select 仓库ID,上级仓库ID,备件编码,数量,(select sum(数量) as 数量 from TEST_1213 t  start with  nvl(t.备件编码,1)=nvl(a.备件编码,1) connect by t.仓库ID=t.上级仓库ID ) as 数量 from 
   TEST_1213 a
    where 上级仓库ID in ('r0')