表和测试数据如下
create table hierartest(id number,parentid number,name varchar2(100),percent number);insert into hierartest values (1,0,'par',0);
insert into hierartest values (2,1,'chi1',12);
insert into hierartest values (3,1,'chi1',12);
insert into hierartest values (4,0,'parn',0);
insert into hierartest values (5,4,'parc',0);
insert into hierartest values (6,5,'c5',12);
insert into hierartest values (7,4,'c4',12);
insert into hierartest values (8,4,'c42',12);
insert into hierartest values (9,1,'chi1',12);
commit;需要的结果是父节点的值是各子节点之和.
1 1 0 par 24
2 2 1 chi1 12
3 3 1 chi1 12
4 4 0 parn 48
5 5 4 parc 12
6 6 5 c5 12
7 7 4 c4 12
8 8 4 c42 12
9 9 1 chi1 12目前进展
select id,parentID,LPAD(' ',3*(LEVEL-1))||Name name, percent from hierartest
connect by prior id = parentid
start with parentID = 0求改良
create table hierartest(id number,parentid number,name varchar2(100),percent number);insert into hierartest values (1,0,'par',0);
insert into hierartest values (2,1,'chi1',12);
insert into hierartest values (3,1,'chi1',12);
insert into hierartest values (4,0,'parn',0);
insert into hierartest values (5,4,'parc',0);
insert into hierartest values (6,5,'c5',12);
insert into hierartest values (7,4,'c4',12);
insert into hierartest values (8,4,'c42',12);
insert into hierartest values (9,1,'chi1',12);
commit;需要的结果是父节点的值是各子节点之和.
1 1 0 par 24
2 2 1 chi1 12
3 3 1 chi1 12
4 4 0 parn 48
5 5 4 parc 12
6 6 5 c5 12
7 7 4 c4 12
8 8 4 c42 12
9 9 1 chi1 12目前进展
select id,parentID,LPAD(' ',3*(LEVEL-1))||Name name, percent from hierartest
connect by prior id = parentid
start with parentID = 0求改良
(SELECT sum(PERCENT) FROM hierartest
WHERE CONNECT_BY_ISLEAF=1
START WITH ID=t.ID
connect by prior id=parentid)percent
from hierartest t;
start with ---从xx开始
connect by prior ----连接条件select ID,parentid,name,percent
from hierartest
where connect_by_isleaf = 1
start with id = 1
connect by prior id = parentid;
SELECT ID,parentid,NAME,
(SELECT sum(PERCENT) FROM hierartest
START WITH ID=t.ID
connect by prior id=parentid)percent
from hierartest t
(SELECT SUM(a.percent)
FROM hierartest a
START WITH a.id = t.id
CONNECT BY PRIOR a.id = a.parentid) sumpercent
FROM hierartest t;
每一条记录都要往下找子节点,没有更好的办法
这里是否非子节点的percent都为0?