with tb as( select 1 parent_id,1 part_id,2 qnty from dual union all select 1, 2, 3 from dual union all select 1, 3, 2 from dual union all select 1, 4, 5 from dual union all select 2, 2, 3 from dual union all select 2, 3, 2 from dual union all select 2, 4, 5 from dual ) select parent_id,part_id, exp(sum(ln(qnty)) over(partition by parent_id order by part_id)) as running_prod from tb PARENT_ID PART_ID RUNNING_PROD ---------- ---------- ------------ 1 1 2 1 2 6 1 3 12 1 4 60 2 2 3 2 3 6 2 4 30
--首先,写个函数来实现求字串乘积 create or replace function func_test2(str in varchar2) return number as num number; begin execute immediate 'select '||str||' from dual' into num; return num; end;--测试数据 with tab as( select 1 parent_id, 1 part_id, 2 qnty from dual union all select 1, 2, 3 from dual union all select 1, 3, 2 from dual union all select 1, 4, 5 from dual ) select func_test2(ltrim(sys_connect_by_path(qnty,'*'),'*')) qnty from tab where connect_by_isleaf=1 start with part_id = 4 --4装到1 ,可以修改此节点 connect by prior parent_id= parent_id and prior part_id=part_id+1
with tb as(
select 1 parent_id,1 part_id,2 qnty from dual union all
select 1, 2, 3 from dual union all
select 1, 3, 2 from dual union all
select 1, 4, 5 from dual union all
select 2, 2, 3 from dual union all
select 2, 3, 2 from dual union all
select 2, 4, 5 from dual )
select parent_id,part_id,
exp(sum(ln(qnty)) over(partition by parent_id order by part_id)) as running_prod
from tb
PARENT_ID PART_ID RUNNING_PROD
---------- ---------- ------------
1 1 2
1 2 6
1 3 12
1 4 60
2 2 3
2 3 6
2 4 30
create or replace function func_test2(str in varchar2)
return number
as
num number;
begin
execute immediate 'select '||str||' from dual' into num;
return num;
end;--测试数据
with tab as(
select 1 parent_id, 1 part_id, 2 qnty from dual
union all
select 1, 2, 3 from dual
union all
select 1, 3, 2 from dual
union all
select 1, 4, 5 from dual
)
select func_test2(ltrim(sys_connect_by_path(qnty,'*'),'*')) qnty from tab
where connect_by_isleaf=1
start with part_id = 4 --4装到1 ,可以修改此节点
connect by prior parent_id= parent_id
and prior part_id=part_id+1