CREATE TABLE basic
(
id number(8) primary key,
name varchar(50) unique,
ismemo char(1) default '0',--备注项不参与累计
parent_id number(8)
)
INSERT INTO basic VALUES(1,'basic1',0,0);
INSERT INTO basic VALUES(2,'basic2',0,1);
INSERT INTO basic VALUES(3,'basic3',1,2);
INSERT INTO basic VALUES(4,'basic4',0,1);
INSERT INTO basic VALUES(5,'basic5',0,4);
INSERT INTO basic VALUES(6,'basic6',0,1);
commit;
CREATE TABLE basic_record
(
id number(8) primary key,
basic_id number(8),
plan_val number,
foreign key(basic_id) references basic(id)
)
--填报表只填报子项目
INSERT INTO basic_record VALUES(1,6,100);
INSERT INTO basic_record VALUES(2,5,200);
INSERT INTO basic_record VALUES(3,3,500);
commit;
希望得到的结果:(子级汇总到父项ismemo不包括在内)
name plan_val
basic1 300
basic2 0
basic3 500
basic4 200
basic5 200
basic6 100
------解决方案--------------------
--子节点和父节点的对应关系
WITH TMP AS
(SELECT ID
,NAME
,ISMEMO
,CONNECT_BY_ISLEAF AS ISFLAG
,CONNECT_BY_ROOT ID AS ROOT
FROM BASIC
WHERE CONNECT_BY_ISLEAF = '1'
CONNECT BY PRIOR ID = PARENT_ID)
SELECT TMP.ROOT
,SUM(CASE
WHEN TMP.ISMEMO = '1' AND TMP.ID <> TMP.ROOT THEN--memo，并且向父节点合计的时候为0
0
ELSE
R.PLAN_VAL
END)
FROM BASIC_RECORD R
JOIN TMP
ON R.BASIC_ID = TMP.ID
GROUP BY TMP.ROOT
ORDER BY TMP.ROOT
试试看可以不
------解决方案--------------------
select root_nm name,
sum(decode(t2.name,
root_nm,
plan_val,
decode(ismemo, '1', 0, plan_val))) plan_val
from basic_record t3,
(select t1.*, connect_by_root t1.name root_nm
from basic t1
connect by prior id = parent_id) t2
where t2.id = t3.basic_id(+)
group by root_nm;