目前只能做到这一步 15:36:17 SQL> select * from ttree start with id='B' or id='C' connect by prior id=pid;ID PID PRICE ---------- ---------- ---------- B A 2 D B 3 G D 2 E B 5 C A 1 F C 2 G C 5已选择7行。已用时间: 00: 00: 00.31 15:37:19 SQL> select * from ttree start with id='B' or id='C' connect by prior id=pid 15:37:39 2 intersect 15:37:45 3 select * from ttree start with id='G' connect by prior pid=id;ID PID PRICE ---------- ---------- ---------- B A 2 C A 1 D B 3 G C 5 G D 2已用时间: 00: 00: 00.32 15:38:07 SQL> 但这里面包含两条主线,A->B->D->G A->C->G 用sql语句很难将其分开,而且还要计算乘积
15:53:52 SQL> select * from t1;I P COUNT1 - - ---------- B A 2 D B 3 E B 5 G D 2 C A 1 F C 2 G C 5已选择7行。已用时间: 00: 00: 00.32 15:54:05 SQL> select sum(a.count1*b.count1) from 15:54:08 2 (select id,pid,count1,level leva from t1 start with id='G' connect by prior pid=id order by level) a, 15:54:08 3 (select id,pid,count1,level levb from t1 start with id='G' connect by prior pid=id order by level) b 15:54:08 4 where a.id=b.pid and a.leva=b.levb+1;SUM(A.COUNT1*B.COUNT1) ---------------------- 17已用时间: 00: 00: 00.00
16:26:57 SQL> select * from t1;I P COUNT1 - - ---------- B A 2 D B 3 E B 5 G D 2 C A 1 F C 2 G C 5已选择7行。已用时间: 00: 00: 00.15 16:27:03 SQL> select nvl(sum(a.count1*b.count1),0) from 16:27:13 2 (select id,pid,count1,level leva from t1 where (id,pid,count1) in (select id,pid,count1 from t1 start with id='G' 16:27:13 3 connect by prior pid=id) start with pid='A' 16:27:13 4 connect by prior id=pid) a, 16:27:13 5 (select id,pid,count1,level levb from t1 where (id,pid,count1) in (select id,pid,count1 from t1 start with id='G' 16:27:13 6 connect by prior pid=id) start with pid='A' 16:27:13 7 connect by prior id=pid) b 16:27:13 8 where a.pid=b.id and a.leva=b.levb+1;NVL(SUM(A.COUNT1*B.COUNT1),0) ----------------------------- 17已用时间: 00: 00: 00.00 16:27:13 SQL> select nvl(sum(a.count1*b.count1),0) from 16:27:26 2 (select id,pid,count1,level leva from t1 where (id,pid,count1) in (select id,pid,count1 from t1 start with id='G' 16:27:26 3 connect by prior pid=id) start with pid='E' 16:27:26 4 connect by prior id=pid) a, 16:27:26 5 (select id,pid,count1,level levb from t1 where (id,pid,count1) in (select id,pid,count1 from t1 start with id='G' 16:27:26 6 connect by prior pid=id) start with pid='E' 16:27:26 7 connect by prior id=pid) b 16:27:26 8 where a.pid=b.id and a.leva=b.levb+1;NVL(SUM(A.COUNT1*B.COUNT1),0) ----------------------------- 0已用时间: 00: 00: 00.16可以修改sql中id和pid的值
结构是灵活的,参数只有根节点,和其中的一个子节点那么以下代码忽略根节点.declare ln_cnt number := 0; ln_tmp number := 0; begin for c in (select level, cnt from t start with id = 'G' connect by prior pid = id) loop if c.level = 1 then ln_cnt := ln_cnt + ln_tmp; ln_tmp := c.cnt; else ln_tmp := ln_tmp * c.cnt; end if; end loop; dbms_output.put_line(ln_cnt); end; /
A有 2*5 个E
需要自定义聚组函数(Oracle9i以上),或是使用PLSQL代码实现.
15:36:17 SQL> select * from ttree start with id='B' or id='C' connect by prior id=pid;ID PID PRICE
---------- ---------- ----------
B A 2
D B 3
G D 2
E B 5
C A 1
F C 2
G C 5已选择7行。已用时间: 00: 00: 00.31
15:37:19 SQL> select * from ttree start with id='B' or id='C' connect by prior id=pid
15:37:39 2 intersect
15:37:45 3 select * from ttree start with id='G' connect by prior pid=id;ID PID PRICE
---------- ---------- ----------
B A 2
C A 1
D B 3
G C 5
G D 2已用时间: 00: 00: 00.32
15:38:07 SQL>
但这里面包含两条主线,A->B->D->G A->C->G
用sql语句很难将其分开,而且还要计算乘积
- - ----------
B A 2
D B 3
E B 5
G D 2
C A 1
F C 2
G C 5已选择7行。已用时间: 00: 00: 00.32
15:54:05 SQL> select sum(a.count1*b.count1) from
15:54:08 2 (select id,pid,count1,level leva from t1 start with id='G' connect
by prior pid=id order by level) a,
15:54:08 3 (select id,pid,count1,level levb from t1 start with id='G' connect
by prior pid=id order by level) b
15:54:08 4 where a.id=b.pid and a.leva=b.levb+1;SUM(A.COUNT1*B.COUNT1)
----------------------
17已用时间: 00: 00: 00.00
- - ----------
B A 2
D B 3
E B 5
G D 2
C A 1
F C 2
G C 5已选择7行。已用时间: 00: 00: 00.15
16:27:03 SQL> select nvl(sum(a.count1*b.count1),0) from
16:27:13 2 (select id,pid,count1,level leva from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:13 3 connect by prior pid=id) start with pid='A'
16:27:13 4 connect by prior id=pid) a,
16:27:13 5 (select id,pid,count1,level levb from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:13 6 connect by prior pid=id) start with pid='A'
16:27:13 7 connect by prior id=pid) b
16:27:13 8 where a.pid=b.id and a.leva=b.levb+1;NVL(SUM(A.COUNT1*B.COUNT1),0)
-----------------------------
17已用时间: 00: 00: 00.00
16:27:13 SQL> select nvl(sum(a.count1*b.count1),0) from
16:27:26 2 (select id,pid,count1,level leva from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:26 3 connect by prior pid=id) start with pid='E'
16:27:26 4 connect by prior id=pid) a,
16:27:26 5 (select id,pid,count1,level levb from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:26 6 connect by prior pid=id) start with pid='E'
16:27:26 7 connect by prior id=pid) b
16:27:26 8 where a.pid=b.id and a.leva=b.levb+1;NVL(SUM(A.COUNT1*B.COUNT1),0)
-----------------------------
0已用时间: 00: 00: 00.16可以修改sql中id和pid的值
ln_cnt number := 0;
ln_tmp number := 0;
begin
for c in (select level, cnt
from t
start with id = 'G'
connect by prior pid = id) loop
if c.level = 1 then
ln_cnt := ln_cnt + ln_tmp;
ln_tmp := c.cnt;
else
ln_tmp := ln_tmp * c.cnt;
end if;
end loop;
dbms_output.put_line(ln_cnt);
end;
/