展开BOM并使用最终用量的算法(转载)

本文系转载子ITPUB，如果有侵犯您权益的地方，烦请及时的告知与我，我即刻将停止侵权行为:

网址：http://www.itpub.net/thread-1020586-1-1.html

        http://www.itpub.net/thread-1020772-3-1.html

        http://www.itpub.net/thread-1020712-1-1.html

       http://stackoverflow.com/questions/12221047/oracle-sql-hierarchical-query-flatten-hierarchy-and-perform-aggregation

      http://stackoverflow.com/questions/4786492/help-calculating-complex-sum-in-hierarchical-dataset/4786672#4786672

LEVEL    Parent   Child    Parent Qty        Child Qty
1            A            B         1                       3
2            B            C         2                       3
3            C            D         5                       6
4            D            E         1                       2
1            A            Z         1                       3

A是成品
B，C，D是半成品
E，Z是原材料

从上面一个比例关系可以计算出，做一个A最终需要10.8个E和3个Z，
也就是能看到下面的结果
Parent  Child   QTY
A           E         10.8
A           Z         3

我想知道有没有什么办法通过一个SQL语句来实现这个功能。

测试表：

CREATE TABLE BOM (PARENT VARCHAR2(10),CHILD VARCHAR2(10),P_QTY NUMBER, C_QTY NUMBER);

INSERT INTO BOM VALUES ('A','B',1,3);
INSERT INTO BOM VALUES ('B','C',2,3);
INSERT INTO BOM VALUES ('C','D',5,6);
INSERT INTO BOM VALUES ('D','E',1,2);
INSERT INTO BOM VALUES ('A','Z',1,3);

COMMIT;

1、使用SQL

 1 SELECT P, D, SUM(QTY)
 2   FROM (SELECT P, C, D, POWER(10, SUM(LOG(10, QTY))) AS QTY
 3           FROM (SELECT DISTINCT P,
 4                                 C,
 5                                 SUBSTR(C, -1, 1) D,
 6                                 REGEXP_SUBSTR(C, '[^,]+', 1, LEVEL),
 7                                 TO_NUMBER(REGEXP_SUBSTR(Q, '[^*]+', 1, LEVEL)) AS QTY
 8                   FROM (SELECT CONNECT_BY_ROOT PARENT AS P,
 9                                SUBSTR(SYS_CONNECT_BY_PATH(CHILD, ','), 2) AS C,
10                                1 || SYS_CONNECT_BY_PATH(C_QTY / P_QTY, '*') AS Q
11                           FROM BOM
12                          WHERE CONNECT_BY_ISLEAF = 1
13                          START WITH PARENT = 'A'
14                         CONNECT BY PARENT = PRIOR CHILD) C
15                 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(Q, '[^*]', '')) + 1
16                  ORDER BY 1, 2) TT
17          GROUP BY P, C, D) FF
18  GROUP BY P, D

2、使用NEWID提供的聚合求积函数

解决思路：
1.把A的每个叶子找出来；
2.顺着叶子往根，一路作乘法上去。

 1 SELECT CHILD,
 2        (SELECT PROD_AGG(C_QTY) / PROD_AGG(P_QTY)
 3           FROM BOM
 4         CONNECT BY PRIOR PARENT = CHILD
 5          START WITH CHILD = INNER.CHILD -- 从每个叶子开始
 6         ) AS C_QTY
 7   FROM (SELECT BOM.*, CONNECT_BY_ISLEAF AS IS_LEAF
 8           FROM BOM
 9         CONNECT BY PRIOR CHILD = PARENT
10          START WITH PARENT = 'A')
11  INNER WHERE IS_LEAF = 1 -- 这个条件找出所有的叶子

其中PROD_AGG 见：
http://www.itpub.net/thread-1020772-1-1.html

CHILD           C_QTY
---------- ----------
E                10,8
Z                   3

不用PRO_AGG的方法：

 1 不用 PROD_AGG的办法：
 2 SELECT CHILD
 3       ,(SELECT POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY))) 
 4           FROM BOM 
 5        CONNECT BY PRIOR PARENT=CHILD 
 6          START WITH CHILD = inner.CHILD     -- 从每个叶子开始
 7         ) AS C_QTY 
 8 FROM (SELECT BOM.*
 9             ,CONNECT_BY_ISLEAF AS IS_LEAF  
10         FROM BOM 
11         CONNECT BY PRIOR CHILD = PARENT 
12        START WITH PARENT='A' 
13      ) inner
14 WHERE IS_LEAF=1;      -- 这个条件找出所有的叶子

3、使用PL/SQL的方法:可以将1替换为connect_by_isleaf：如果为叶子节点，则该函数的值1，否则为0，刚好可以替代1，

1 SELECT CHILD, GET_EXPRESSION_RSLT(CON_QTY)
2   FROM (SELECT CHILD,
3                LEVEL M,
4                1 || SYS_CONNECT_BY_PATH(C_QTY / P_QTY, '*') CON_QTY
5           FROM BOM
6          WHERE CONNECT_BY_ISLEAF = 1
7          START WITH PARENT = 'A'
8         CONNECT BY PARENT = PRIOR CHILD) A

结果：
1 E 10.8
2 F 3.85714285714285714285714285714285714287
3 Z 3

自定义函数：

 1 CREATE OR REPLACE FUNCTION GET_EXPRESSION_RSLT(I_EXPRESSION VARCHAR2) RETURN VARCHAR2 IS
 2  /************************************************************
 3   * 函数名称：GET_EXPRESSION_RSLT
 4   * 功能描述：获取指定的表达式的结果
 5   * 参数：I_EXPRESSION :表达式  例如：1*2*3
 6   * 编 写 人：XXX
 7   * 编写时间：XXXX-XX-XX
 8   * 修改记录：
 9   *************************************************************/
10     RETURNSTR VARCHAR2(500) := '';
11     EXECSQL   VARCHAR2(4000) := '';
12   BEGIN
13     EXECSQL := ' SELECT ' || I_EXPRESSION || '  FROM  DUAL';
14     EXECUTE IMMEDIATE (EXECSQL)
15       INTO RETURNSTR;
16     RETURN RETURNSTR;
17   END;

如果要看A用了每个B,C,D,E
则只需要将CONNECT_BY_SILEFT=1去掉即可

也可以使用下面的语句：

1 SELECT CHILD, dbms_aw.eval_number(CON_QTY)
2   FROM (SELECT CHILD,
3                LEVEL M,
4                1 || SYS_CONNECT_BY_PATH(C_QTY / P_QTY, '*') CON_QTY
5           FROM BOM
6          WHERE CONNECT_BY_ISLEAF = 1
7          START WITH PARENT = 'A'
8         CONNECT BY PARENT = PRIOR CHILD) A

其中dbms_aw.eval_number这个函数是用来解析字符串函数的

 或者使用with函数

 1 WITH H AS
 2  (SELECT SYS_CONNECT_BY_PATH(CHILD, '/') NAVPATH,
 3          CHILD,
 4          QUANTITY QTY,
 5          ISLEAF
 6     FROM ITEMHIER
 7    START WITH PARENT = 'ASSY001'
 8   CONNECT BY PRIOR CHILD = PARENT)
 9 SELECT H1.NAVPATH, H1.CHILD,(
10 SELECT /*EXP(SUM(LN(H2.QTY))),*/
11 POWER(10, SUM(LOG(10, QTY)))
12   FROM H H2
13  WHERE INSTR(H1.NAVPATH, H2.NAVPATH) = 1) QTY
14   FROM H H1
15  WHERE ISLEAF = 1

 

转载于:https://www.cnblogs.com/caroline/p/3428385.html