狂浪修改后的语句是这样子的:但还没有加入我最后的一个条件. 从树根到截止结点,所有经过的结点都相乘,存放在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 /更正一下我的提问,修改了一下数据库,要求达到这个的结果,有办法吗?
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';
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';
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 /更正一下我的提问,修改了一下数据库,要求达到这个的结果,有办法吗?
自己建个函数
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';