这个层级汇总SQL如何写？

Question

本帖最后由 huang_w 于 2012-07-30 20:33:40 编辑

Answer 1

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;

Answer 2

谢谢楼上的回复， 不过这种写法跑太慢了， 我跑了一个多小时也没跑出来
有没有更优化的SQL？

Answer 3

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 ;看这样类？

Answer 4

一楼的SQL 好像不对，虽然模拟的SQL结果正确的，但是我用到实际的表结果就不对。
一下是我的SQL
--------------------
select t2.acc_date,
       t2.itm_no,
       t2.supitm_no,
      (select sum(debamt)
          from tempsum t
          where t.acc_date= t2.acc_date
         start with t.itm_no = t2.itm_no
        connect by t.supitm_no = prior t.itm_no) debamt,
       (select sum(creamt)
          from tempsum t
          where t.acc_date= t2.acc_date
         start with t.itm_no = t2.itm_no
        connect by t.supitm_no = prior t.itm_no) creamt,
       (select sum(debblc)
          from tempsum t
          where t.acc_date= t2.acc_date
         start with t.itm_no = t2.itm_no
        connect by t.supitm_no = prior t.itm_no) debblc,
        (select sum(creblc)
          from tempsum t
          where t.acc_date= t2.acc_date
         start with t.itm_no = t2.itm_no
        connect by t.supitm_no = prior t.itm_no) creblc
  from tempsum t2 
  where acc_date= to_date('2012-01-01', 'yyyy-mm-dd') 
  order by t2.itm_no;
--------------------
结果是：1 2012/1/1 1001 0 16735487.02 7146803.86 273721315.66 0
2 2012/1/1 100101 1001 536737.41 229269.8 8766981.35 0
3 2012/1/1 100101001 100101 5755.44 147.28 140001.04 0
4 2012/1/1 100101002 100101 4994.41 1001.61 125957.35 0
5 2012/1/1 100101003 100101 6564.26 6246.91 16847.46 0
6 2012/1/1 100102 1001 3117.01 1272.26 62738.51 0
7 2012/1/1 1002 0 12776030.62 9412611.22 109944775.46 0
8 2012/1/1 100201 1002 194118.28 91877.8 3351990.94 0
9 2012/1/1 100201001 100201 4383.32 2074.66 75690.25 0
10 2012/1/1 100201002 100201 1878.56 889.14 32438.49 0
11 2012/1/1 100202 1002 4117.01 1272.26 82738.51 0

Answer 5

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