求一句按父节点统计的SQL
表和测试数据如下
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
求改良