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
)
select *
from t s
order by 仓库ID
希望得到的结果是 :
仓库ID 上级仓库ID 备件编码 数量
r1 r0 0001 3
r1 r0 0002 9
r1 r0 0004 9
r1 r1 0005 2
r1 r1 0006 2