有个一个表,如下
NAME P_NAME NUM
a -1 3
b -1 4
c a 5
d b 6
e c 7
f d 8
g e 9我想得到如下
NAME P_NAME NUM
a -1 24
b -1 18其中 24=3+5+7+9
18= 4+6+8
感觉需要在递归中求和,请高手指点下。
NAME P_NAME NUM
a -1 3
b -1 4
c a 5
d b 6
e c 7
f d 8
g e 9我想得到如下
NAME P_NAME NUM
a -1 24
b -1 18其中 24=3+5+7+9
18= 4+6+8
感觉需要在递归中求和,请高手指点下。
NAME,P_NAME,
(select sum(NUM) from t connect by P_NAME = prior NAME start with NAME= t0.NAME) NUM
from
t t0
where
P_NAME=-1;
NAME varchar2(20), P_NAME varchar2(20), NUM int);insert into test values('a','-1',3 );
insert into test values('b','-1',4 );
insert into test values('c','a',5 );
insert into test values('d','b',6 );
insert into test values('e','c',7 );
insert into test values('f','d',8 );
insert into test values('g','e',9 );
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;
输出:
NAME P_NAME NUM
b -1 18
a -1 24
(select sum(NUM) from t connect by P_NAME = prior NAME start with NAME= t0.NAME) NUM
from t t0
where P_NAME='-1' 这种写法是可以做出来的,但只适合数据量少的,数据量很大速度就不行了