问题描述：
有两个表，表A 单位表，三级单位，通过parentid进行级别间的关联
表B 统计值表，二三级单位会有一个value值。
想要的结果：表A和表B关联，要求对二级单位分组汇总value值，包括二级单位的value值和parentid为二级单位的value值。
比如A-1单位，获得A-1的value+code为12324的value+code为32434的vaue
附带建表语句的插入语句
--A表
create table A (code varchar2(10),parentcode varchar2(10));
--第一级别
insert into A values('1506001','');
--第二级别
insert into A values('A-1','1506001');
insert into A values('B-1','1506001');
insert into A values('A-2','1506001');
insert into A values('C-1','1506001');
--第三级别
insert into A values('12324','A-1');
insert into A values('32434','A-1');
insert into A values('aaaaa','A-2');
insert into A values('ccccc','C-1');
insert into A values('bbbbb','B-1');
DELETE FROM A
select * from A
--B表
--二级可能有数据，也可能没有
create table B(code varchar2(10),value int);
insert into B values('A-1',200);
insert into B values('B-1',50);
insert into B values('12324',522);
insert into B values('32434',500);
insert into B values('aaaaa',100);
insert into B values('ccccc',200);
insert into B values('bbbbb',300);
------解决方案--------------------
- SQL code
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
------解决方案--------------------
- SQL code
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)