树状结构数据的乘积怎么处理?

[想入]

我有一个表，是树状结构
parent child child_type
------------------------
p1 c1 normal
p1 c2 special
c2 s1 normal
s1 n1 normal
c2 s2 special
s2 D1 normal
s2 D2 special
D2 E1 normal
D2 E2 special
. . .
. . .
. . .
c2 s3 normal


结构如下
p1
┣ c1
┗ c2
┣ s1
┃ ┗ n1
┃
┣ s2
┃ ┣ D1
┃ ┗ D2
┃ ┣ E1
┃ ┗ E2
┃ ┣ .....
┃ ┗ .....
┗ s3
我想要下面的查询结果
PARENT CHILD QUANTITY CHILD_TYPE COUNT

p1 c1 a1 normal a1
p1 c2 a2 special /
c2 s1 a3 normal a2*a3
s1 n1 a4 normal /
c2 s2 a5 special /
s2 D1 a6 normal a2*a5*a6
s2 D2 a7 special /
D2 E1 a8 normal a2*a5*a7*a8
D2 E2 a9 special /

说明：把子类型为normal的数据全部显示出来,如果子类型为special,继续向树的树叉查询,直至查询到子类型为normal的数据.
需要注意的是normal类型的数据的所有下层都不显示，即遇到normal类型的数据该树的这个分支就截止，如n1 ,也就是假如C1,C2为normal的数据,则只显示树根P1.

从树根到截止结点,所有经过的结点都相乘,存放在COUNT字段中,怎么处理
注:划"/"的记录只作下一层参考之用,并不在查询结果的记录中显示.

[想入]

狂浪修改后的语句是这样子的：但还没有加入我最后的一个条件. 从树根到截止结点,所有经过的结点都相乘,存放在COUNT字段中
select * from
(select max(parent)keep(dense_rank last order by level)parent,
max(child)keep(dense_rank last order by level)child,
max(child_type)keep(dense_rank last order by level)child_type
from table1
start with parent='p1'
connect by prior child=parent
and prior child_type='special'
group by rownum-level)
where child_type <>'special';

[想入]

PARENT CHILD QUANTITY CHILD_TYPE COUNT

p1 c1 a1 normal a1
p1 c2 a2 special /
c2 s1 a3 normal a2*a3
s1 n1 a4 normal /
c2 s2 a5 special /
s2 D1 a6 normal a2*a5*a6
s2 D2 a7 special /
D2 E1 a8 normal a2*a5*a7*a8
D2 E2 a9 special /

更正一下我的提问,修改了一下数据库,要求达到这个的结果,有办法吗?

[小灰狼W]

oracle自带函数不能实现连乘的功能
自己建个函数

create or replace function functest1(str in varchar2)return number
as
num number;
begin
execute immediate 'select '||replace(substr(str,2),',','*')||' from dual' into num;
return num;
exception
when others then
return -1;
end;

查询

select * from 
(select max(parent)keep(dense_rank last order by level)parent, 
  max(child)keep(dense_rank last order by level)child, 
  max(child_type)keep(dense_rank last order by level)child_type ,
  functest1(max(sys_connect_by_path(quantity,','))keep(dense_rank last order by level))count
from table1 
start with parent='p1' 
connect by prior child=parent 
  and prior child_type='special' 
group by rownum-level) 
where child_type <>'special';

[小灰狼W]

这里可以直接用'*'连接字符串，在函数里就不需要再作替换..

[想入]

好的,我在VB里面试试.