求一句分层汇总SQL

[allysniper]

请问下我如何用SQL实现分层汇总查询。比如001001001 金额是100，001001002 金额是200，我要用SQL处理以后，多二条记录，其中001001是300，001也是300。

[AcHerat]

-- 要数据！

[allysniper]

code amt
001001001 100
001001002 200
汇总后：
code amt
001 300
001001 300
001001001 100
001001002 200

[lxyzxq2008]

with tab as
(
select '001' A, '001' B, '001' C, 100 money from dual union all
select '001','001','002',200 from dual
)
--select * from tab
select A || B || C sid, sum(money) money from tab group by A || B || C
union all
select A || B sid, sum(money) from tab group by A || B 
union all
select A sid, sum(money) from tab group by A
-----------------------------
sid            money
001001001	100
001001002	200
001001	        300
001	        300

[lxyzxq2008]

with tab as
(
select '001' A, '001' B, '001' C, 100 money from dual union all
select '001','001','002',200 from dual
)
--select * from tab
select A || B || C code, sum(money) amt from tab group by A || B || C
union all
select A || B code, sum(money) amt from tab group by A || B 
union all
select A code, sum(money) amt from tab group by A
order by code

[lxyzxq2008]

--结果
code amt
001 300
001001 300
001001001 100
001001002 200

[UPC子夜]

select code,sum(aim) from dual group by code
union
select substr(code,1,Length(code)-3),sum(aim) from dual where Length(code)-3 > 0 group by substr(code,1,Length(code)-3)
union
select substr(code,1,Length(code)-6),sum(aim) from dual where Length(code)-6 > 0 group by substr(code,1,Length(code)-6)
没有数据，没法测试，不过大概就是这样，你自己再改改吧

[lxyzxq2008]

--或者
with tab as
(
select '001001001' code, 100 money from dual union all
select '001001002',200 from dual
)
select code, sum(money) from tab group by code
union all
select substr(code,1,6) code, sum(money) from tab group by substr(code,1,6)
union all
select substr(code,1,3) code, sum(money) from tab group by substr(code,1,3)
order by code
-----------------------------------------------
code    sum(money)
001	300
001001	300
001001001	100
001001002	200

[allysniper]

楼上的方法适用于长度一样的情况，但我还想问下，如果我的初始数据增加一条数据001003 200，如何将结果变为

code sum(money)
001 500
001001 300
001003 200
001001001 100
001001002 200

[真的不知道取什么名]

[Quote=引用 8 楼 allysniper 的回复:]
楼上的方法适用于长度一样的情况，但我还想问下，如果我的初始数据增加一条数据001003 200，如何将结果变为

code sum(money)
001 500
001001 300
001003 200
001001001 100
001001002 200
[/Quote]

 with tab as
 (
 select '001001001' code, 100 money from dual union all
 select '001001002',200 from dual union all
 select '001003',200 from dual
 )
 select distinct *
 from (
 select code, sum(money) from tab group by code
 union all
 select substr(code,1,6) code, sum(money) from tab group by substr(code,1,6)
 union all
 select substr(code,1,3) code, sum(money) from tab group by substr(code,1,3)
 )
 order by length(code),code


CODE         SUM(MONEY)
------------ ----------
001                 500
001001              300
001003              200
001001001           100
001001002           200