日期:2014-05-16  浏览次数:20816 次

这个层级汇总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 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.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 code

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.subid
group by t1.id) tt,t
where tt.id(+)=t.id
order by 1