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

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 /

...全文
94 5 打赏 收藏 举报

5 条回复

• 打赏
• 举报

• 打赏
• 举报

• 打赏
• 举报

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

• 打赏
• 举报

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
connect by prior child=parent
and prior child_type='special'
group by rownum-level)
where child_type <>'special';

3471

Oracle 高级技术相关讨论专区

2010-01-21 02:16