日期:2014-05-17 浏览次数:21058 次
Select t1.Parentcode, Sum(t1.Value) From (Select t.Code code, Decode(t.Parentcode, '1506001', t.Code, t.Parentcode) Parentcode, t.Value Value From (Select a.Code Code, a.Parentcode Parentcode, Decode(b.Value, '', 0, b.Value) Value From a, b Where a.Code = b.Code(+)) t Start With t.Parentcode = '1506001' Connect By t.Parentcode = Prior t.Code)t1 Group By t1.Parentcode
------解决方案--------------------
Select t1.Parentcode, Sum(t1.Value)
From (Select t.Code code,
Decode(t.Parentcode, '1506001', t.Code, t.Parentcode) Parentcode,
t.Value Value
From (Select a.Code Code,
a.Parentcode Parentcode,
Decode(b.Value, '', 0, b.Value) Value
From a, b
Where a.Code = b.Code(+)) t
Start With t.Parentcode = '1506001'
Connect By t.Parentcode = Prior t.Code)t1
Group By t1.Parentcode
------解决方案--------------------
SELECT substr(p, 2, instr(p, ',', 1, 2) - 2) code, SUM(VALUE) FROM (SELECT LEVEL, a.*, b.value, sys_connect_by_path(a.code, ',') || ',' p FROM a, b WHERE a.code = b.code(+) START WITH a.parentcode = '1506001' CONNECT BY PRIOR a.code = a.parentcode) GROUP BY substr(p, 2, instr(p, ',', 1, 2) - 2)