oracle递归父子求和,oracle 递归 求和

create table test( NAME varchar2(20), P_NAME varchar2(20), NUM int);

insert into test values('1','-1',3 );

insert into test values('11','1',5 );

insert into test values('111','11',6 );

insert into test values('1111','111',9 );

insert into test values('2','-1',4 );

insert into test values('21','2',7 );

insert into test values('211','21',8 );

COMMIT;

SELECT NAME, P_NAME, SUM(NUM) num

FROM (

SELECT NUM, CONNECT_BY_ROOT NAME NAME, CONNECT_BY_ROOT P_NAME P_NAME

FROM TEST S

CONNECT BY PRIOR NAME = P_NAME

--START WITH P_NAME = '-1'

)

GROUP BY NAME, P_NAME order by name;