4階以上的BOM個人建議還是搭配function或producer比較簡單
或許還有更好的方式
create table test(main varchar2(10),sub varchar2(10),count number);
insert into test values('A','B',2);
insert into test values('A','J',4);
insert into test values('B','C',3);
insert into test values('B','D',2);
insert into test values('A','F',5);
insert into test values('A','G',6);
insert into test values('C','H',7);
insert into test values('D','I',2);
insert into test values('I','K',2);
insert into test values('J','L',2);
insert into test values('K','P',2);
create or replace function f_test(p_main varchar2,p_sub varchar)
return number is
v_main varchar2(10);
v_sub varchar2(10);
v_count number;
v_prior_main varchar2(10);
v_prior_sub varchar2(10);
BEGIN
SELECT main,sub,count,prior main prior_main,prior sub prior_sub
INTO v_main,v_sub,v_count,v_prior_main,v_prior_sub
FROM test
WHERE main = p_main
AND sub = p_sub
START WITH main = 'A'
CONNECT BY main = prior sub;
IF v_prior_main IS NULL AND v_prior_sub IS NULL THEN
RETURN v_count;
ELSE
v_count := v_count * f_test(v_prior_main,v_prior_sub);
RETURN v_count;
END IF;
END;
SELECT main,sub,COUNT,count*nvl(f_test(prior main,prior sub),1)
FROM test
START WITH main = 'A'
CONNECT BY main = prior sub
ORDER BY main,sub;
這應該是你想要的答案