这个层级汇总SQL如何写？
有个科目表t 目前只有最底层级有数据，上级都为0
我想做往上汇总，最多是5级，4级等于5级的汇总，3级等于4级的汇总，依次类推
itm_no, supitm_no, amt1, amt2, amt3
1001 0 0 0 0
100101 1001 0 0 0
100101001 100101 2 5 3
100101002 100101 1 5 2
100102 1001 0 0 0
1002 0 0 0 0
....
科目表中逐级向上汇总
如1001 的金额amt1, amt2 , amt3 等于 100101 + 100102 的金额
100101 等于 100101001 + 100101002
这个SQL如何写？谢谢！
[解决办法]
with t as (SELECT '1001' itm_no, '0' supitm_no, 0 amt1, 0 amt2, 0 amt3 FROM DUAL UNION ALL SELECT '100101', '1001', 0, 0, 0 FROM DUAL UNION ALL SELECT '100101001', '100101', 2, 5, 3 FROM DUAL UNION ALL SELECT '100101002', '100101', 1, 5, 2 FROM DUAL UNION ALL SELECT '100102', '1001', 0, 0, 0 FROM DUAL )select t2.*, (select sum(amt1) from t start with t.itm_no = t2.itm_no connect by t.supitm_no = prior t.itm_no) amt1, (select sum(amt2) from t start with t.itm_no = t2.itm_no connect by t.supitm_no = prior t.itm_no) amt2, (select sum(amt3) from t start with t.itm_no = t2.itm_no connect by t.supitm_no = prior t.itm_no) amt3 from t t2;
[解决办法]
with t as (SELECT '1001' itm_no, '0' supitm_no, 0 amt1, 0 amt2, 0 amt3 FROM DUAL UNION ALL SELECT '100101', '1001', 0, 0, 0 FROM DUAL UNION ALL SELECT '100101001', '100101', 2, 5, 3 FROM DUAL UNION ALL SELECT '100101002', '100101', 1, 5, 2 FROM DUAL UNION ALL SELECT '100102', '1001', 0, 0, 0 FROM DUAL ) select t2.itm_no, sum(t1.amt1), sum(t1.amt2), sum(t1.amt3) from t t1, t t2 where t1.itm_no in (select itm_no from t start with t.itm_no = t2.itm_no connect by t.supitm_no = prior t.itm_no) group by t2.itm_no ;
[解决办法]
with t as( select 1001 id,0 subid,0 amt1,0 amt2,0 amt3 from dual union all select 100101,1001,0,0,0 from dual union all select 100101001,100101,2,5,3 from dual union all select 100101002,100101,1,5,2 from dual union all select 100102,1001,0,0,0 from dual)select t.id,t.subid,nvl(tt.a1,0) amt1,nvl(tt.a2,0) amt2,nvl(tt.a3,0) amt3 from (select t1.id,sum(t2.amt1) a1,sum(t2.amt2) a2,sum(t2.amt3) a3 from t t1,t t2 where t1.id=t2.subidgroup by t1.id) tt,twhere tt.id(+)=t.idorder by 1
