作者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/cn.aspx?n=bbs/Database/M.1438264123.A.5FB.html
1F：推 moyasi: 10g测试结果是错的 11g测试结果是对的 07/31 11:33
2F：推 cutekid: 好强大(Y) 07/31 12:00