统计树节点下某个节点的个数量，分数不够，可以再加

[wangj2001]

比如
A
/ \
B C
/ \ / \
D E F G
/ \
G H

表结构如下
id, Pid ,Count
-------------------
B A 2
D B 3
E B 5
G D 2
C A 1
F C 2
G C 5
G H 3
................

统计出A结构树中有多少个G

如果解决了外加100分

[bzszp]

select count(*) from
(select id from tbname start with id='A'
connect by prior id=pid
) t
where t.id='G';

[wangj2001]

这样不对，父子是种乘积的关系

[zmgowin]

select count(*) from t1 where id='G' start with pid='A' connect by prior id=pid;

[bzszp]

父子是种乘积的关系
什么意思？

[nyfor]

题意不懂,能不能举例说明你要的结果啊

[sakas]

父子是种乘积的关系 就是比如说A的有2*3+5*1个G吧

[wangj2001]

如A有 2*3*2+5*1 个G
A有 2*5 个E

[zmgowin]

还是写过程吧

[wangj2001]

B中有5个E,A中有2个B 那么A中就有5*2个E

[nyfor]

由于目前没有做乘积的聚组函数,所以单凭SQL恐怕实现不了.
需要自定义聚组函数(Oracle9i以上),或是使用PLSQL代码实现.

[bzszp]

目前只能做到这一步
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语句很难将其分开，而且还要计算乘积

[zmgowin]

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

[wangj2001]

结构是灵活的，参数只有根节点，和其中的一个子节点

[zmgowin]

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的值

[nyfor]

结构是灵活的，参数只有根节点，和其中的一个子节点

那么以下代码忽略根节点.

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;
/

[timeblink]

关注ing