作者rongkuo (small kuo)
看板Database
標題Re: [SQL ] Oracle connect by 語法,數量累乘
時間Thu Jul 30 21:48:41 2015
※ 引述《rongkuo (small kuo)》之銘言：
: ※ 引述《bohei (run and fall)》之銘言：
: : 大家好
: : 目前正在做一個BOM的展開
: : 使用的是connecy by prior 語法 目前唯獨QPA無法算出
: : 想用累乘的概念把用量算出來 下面是簡單的例子
: : 主料號 子料號 用量
: : ====================
: : A B 2
: : B C 3
: : SELECT 子料號,用量 FROM table
: : WHERE connect_by_isleaf=1
: : START WITH 主料號 = 'A'
: : CONNECT BY PRIOR 子料號 = 主料號
: : 希望最後出來的結果是
: : 子料號 用量
: : ============
: : C 6
: : 不知道有沒有辦法做到? 先謝謝大家!
使用MODEL子句：
CREATE TABLE A
(
C1 VARCHAR2(10),
C2 VARCHAR2(10),
C3 NUMBER
);
INSERT INTO A (C1, C2, C3) VALUES ('A', 'B', 2);
INSERT INTO A (C1, C2, C3) VALUES ('A', 'C', 3);
INSERT INTO A (C1, C2, C3) VALUES ('C', 'D', 4);
INSERT INTO A (C1, C2, C3) VALUES ('C', 'E', 5);
COMMIT;
SELECT C2,PROD2
FROM (
SELECT *
FROM (
SELECT C1
, C2
, C3
, CONNECT_BY_ISLEAF LEAF
, SYS_CONNECT_BY_PATH(C2,',') PA
, COUNT(*)OVER() CNT
FROM A
START WITH C1='A'
CONNECT BY PRIOR C2 = C1)
MODEL
DIMENSION BY (ROW_NUMBER() OVER(ORDER BY C1,C2,C3) RN)
MEASURES(C1,C2,C3,LEAF,PA,CNT,1 PROD1,1 TMP,1 PROD2)
RULES ITERATE(10000) UNTIL (ITERATION_NUMBER>=CNT[1]-1)
(
TMP[ANY]=CASE WHEN REGEXP_LIKE(PA[ITERATION_NUMBER+1],C2[CV()]) THEN C3[CV()]
ELSE 1 END,
PROD1[ANY]=EXP(SUM(LN(TMP)) OVER(ORDER BY RN
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)),
PROD2[ITERATION_NUMBER+1]=PROD1[1]
)
)
WHERE LEAF=1
ORDER BY 1;
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 111.255.232.194
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Database/M.1438264123.A.5FB.html
1F：推 moyasi: 10g測試結果是錯的 11g測試結果是對的 07/31 11:33
2F：推 cutekid: 好強大(Y) 07/31 12:00