这个层级汇总SQL怎么写_Oracle技术

这个层级汇总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如何写？谢谢！

------解决方案--------------------

SQL code

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;
------解决方案--------------------
SQL codewith 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 ;
------解决方案--------------------
SQL codewith 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