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,
dept_id number(8), --部门
basic_id number(8),
plan_val number,
foreign key(basic_id) references basic(id)
)
--填报表只填报最子项目，父节点值等于其子节点合计值。
INSERT INTO basic_record VALUES(1,1,6,100);
INSERT INTO basic_record VALUES(2,1,5,200);
INSERT INTO basic_record VALUES(3,1,3,500);
INSERT INTO basic_record VALUES(1,2,6,300);
INSERT INTO basic_record VALUES(2,2,5,100);
INSERT INTO basic_record VALUES(3,2,3,300);
commit;
希望得到的结果:(子级汇总到父项ismemo=1不参与合计)
name plan_val dept_id
basic1 300 1
basic2 0 1
basic3 500 1
basic4 200 1
basic5 200 1
basic6 100 1
basic1 400 2
basic2 0 2
basic3 300 2
basic4 100 2
basic5 100 2
basic6 300 2
------解决方案--------------------