本文系转载子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://*.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
SELECT P, D, SUM(QTY)
FROM (SELECT P, C, D, POWER(10, SUM(LOG(10, QTY))) AS QTY
FROM (SELECT DISTINCT P,
C,
SUBSTR(C, -1, 1) D,
REGEXP_SUBSTR(C, '[^,]+', 1, LEVEL),
TO_NUMBER(REGEXP_SUBSTR(Q, '[^*]+', 1, LEVEL)) AS QTY
FROM (SELECT CONNECT_BY_ROOT PARENT AS P,
SUBSTR(SYS_CONNECT_BY_PATH(CHILD, ','), 2) AS C,
1 || SYS_CONNECT_BY_PATH(C_QTY / P_QTY, '*') AS Q
FROM BOM
WHERE CONNECT_BY_ISLEAF = 1
START WITH PARENT = 'A'
CONNECT BY PARENT = PRIOR CHILD) C
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(Q, '[^*]', '')) + 1
ORDER BY 1, 2) TT
GROUP BY P, C, D) FF
GROUP BY P, D
2、使用NEWID提供的聚合求积函数
解决思路：
1.把A的每个叶子找出来；
2.顺着叶子往根，一路作乘法上去。
SELECT CHILD,
(SELECT PROD_AGG(C_QTY) / PROD_AGG(P_QTY)
FROM BOM
CONNECT BY PRIOR PARENT = CHILD
START WITH CHILD = INNER.CHILD -- 从每个叶子开始
) AS C_QTY
FROM (SELECT BOM.*, CONNECT_BY_ISLEAF AS IS_LEAF
FROM BOM
CONNECT BY PRIOR CHILD = PARENT
START WITH PARENT = 'A')
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的方法：
不用 PROD_AGG的办法：
SELECT CHILD
,(SELECT POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
FROM BOM
CONNECT BY PRIOR PARENT=CHILD
START WITH CHILD = inner.CHILD -- 从每个叶子开始
) AS C_QTY
FROM (SELECT BOM.*
,CONNECT_BY_ISLEAF AS IS_LEAF
FROM BOM
CONNECT BY PRIOR CHILD = PARENT
START WITH PARENT='A'
) inner
WHERE IS_LEAF=1; -- 这个条件找出所有的叶子
3、使用PL/SQL的方法:可以将1替换为connect_by_isleaf：如果为叶子节点，则该函数的值1，否则为0，刚好可以替代1，
SELECT CHILD, GET_EXPRESSION_RSLT(CON_QTY)
FROM (SELECT CHILD,
LEVEL M,
1 || SYS_CONNECT_BY_PATH(C_QTY / P_QTY, '*') CON_QTY
FROM BOM
WHERE CONNECT_BY_ISLEAF = 1
START WITH PARENT = 'A'
CONNECT BY PARENT = PRIOR CHILD) A
结果：
1 E 10.8
2 F 3.85714285714285714285714285714285714287
3 Z 3
自定义函数：
CREATE OR REPLACE FUNCTION GET_EXPRESSION_RSLT(I_EXPRESSION VARCHAR2) RETURN VARCHAR2 IS
/************************************************************
* 函数名称：GET_EXPRESSION_RSLT
* 功能描述：获取指定的表达式的结果
* 参数：I_EXPRESSION :表达式 例如：1*2*3
* 编 写 人：XXX
* 编写时间：XXXX-XX-XX
* 修改记录：
*************************************************************/
RETURNSTR VARCHAR2(500) := '';
EXECSQL VARCHAR2(4000) := '';
BEGIN
EXECSQL := ' SELECT ' || I_EXPRESSION || ' FROM DUAL';
EXECUTE IMMEDIATE (EXECSQL)
INTO RETURNSTR;
RETURN RETURNSTR;
END;
如果要看A用了每个B,C,D,E
则只需要将CONNECT_BY_SILEFT=1去掉即可
也可以使用下面的语句：
SELECT CHILD, dbms_aw.eval_number(CON_QTY)
FROM (SELECT CHILD,
LEVEL M,
1 || SYS_CONNECT_BY_PATH(C_QTY / P_QTY, '*') CON_QTY
FROM BOM
WHERE CONNECT_BY_ISLEAF = 1
START WITH PARENT = 'A'
CONNECT BY PARENT = PRIOR CHILD) A
其中dbms_aw.eval_number这个函数是用来解析字符串函数的
或者使用with函数
WITH H AS
(SELECT SYS_CONNECT_BY_PATH(CHILD, '/') NAVPATH,
CHILD,
QUANTITY QTY,
ISLEAF
FROM ITEMHIER
START WITH PARENT = 'ASSY001'
CONNECT BY PRIOR CHILD = PARENT)
SELECT H1.NAVPATH, H1.CHILD,(
SELECT /*EXP(SUM(LN(H2.QTY))),*/
POWER(10, SUM(LOG(10, QTY)))
FROM H H2
WHERE INSTR(H1.NAVPATH, H2.NAVPATH) = 1) QTY
FROM H H1
WHERE ISLEAF = 1