对BOM展开并计算最终使用量的解答

淡默```

帮顶!

VagrantLee

原来你还有工作的啊！不是只混水的啊！

马鞍

受益匪浅

missingsky

哈哈哈
恭喜受精了啊

ljkcaizi

ORACLE EBS的BOM上也有这个问题,前几天我作一报表计算机BOM物料成本,需要把BOM展开
我写了一个递归函数,效果还不错
庆幸的是BOM层次不多,执行效率还可以
两万条记录用了不到五秒钟

不过总觉得用递归函数还是不太合适,前些天也发帖求助,现在还没明确答复

看了楼上的讨论,有点启示,不过有些语法还看不太明白,继续研究ing...

ganghz

学习了

PUB-CEO

路过  不错的帖子

myoekk

newkid 发表于 2008-7-15 22:22
如果你同时要求A,B的原料数，那么起点就必须有两个，而且所有数字都要按它们来分组。修改如下：

WITH vw ...

小弟在引用大神这段语句时，多加了几层BOM结构测试，就是非叶子节点不唯一的情况，算出来的原料数量有点问题，这种情况该怎么处理，下面是结果有问题的例子，还望大神指导一下：
WITH bom1 AS( SELECT 'a' PARENT, 'b' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'b' PARENT, 'c' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'b' PARENT, 'g' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'c' PARENT, 'd' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'a' PARENT, 'e' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'e' PARENT, 'b' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'e' PARENT, 'f' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'a' PARENT, 'h' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'a1' PARENT,'b' CHILD, 1 P_QTY, 2 C_QTY FROM dual
                          ),
vw_bom AS (SELECT DISTINCT b.*,CONNECT_BY_ROOT(parent) AS ROOT
                FROM bom1 b
                CONNECT BY PRIOR CHILD = PARENT
               START WITH PARENT IN ('a','a1')
               )
SELECT ROOT
      ,CHILD
      ,SUM(C_QTY) AS C_QTY       -- 解决叶子（原料）不唯一
  FROM (SELECT ROOT
              ,CHILD
              ,(SELECT POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
                  FROM vw_bom
               CONNECT BY PRIOR PARENT=CHILD AND ROOT = inner.ROOT
                 START WITH CHILD = inner.CHILD     -- 从每个叶子开始
                            AND PARENT = inner.PARENT    -- 解决叶子（原料）不唯一
                            AND ROOT = inner.ROOT
                ) AS C_QTY
        FROM vw_bom inner
       WHERE NOT EXISTS (SELECT 1 FROM vw_bom WHERE parent = inner.child)
      )
GROUP BY ROOT,CHILD;
结果：
RO C      C_QTY
-- - ----------
a  h          2
a  f          4
a  d         32
a  g         16
a1 g          4
a1 d          8
其中 a 用 d 32个，手算出来是24，a 用 g 16个，手算是12个。

newkid

myoekk 发表于 2016-9-8 14:52
小弟在引用大神这段语句时，多加了几层BOM结构测试，就是非叶子节点不唯一的情况，算出来的原料数量有点 ...

你这个已经是图不是树了，应该用路径去匹配：

           WITH bom1 AS( SELECT 'a' PARENT, 'b' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'b' PARENT, 'c' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'b' PARENT, 'g' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'c' PARENT, 'd' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'a' PARENT, 'e' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'e' PARENT, 'b' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'e' PARENT, 'f' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'a' PARENT, 'h' CHILD, 1 P_QTY, 2 C_QTY FROM dual UNION ALL
                         SELECT 'a1' PARENT,'b' CHILD, 1 P_QTY, 2 C_QTY FROM dual
                          ),
vw_bom AS (SELECT  b.*,CONNECT_BY_ROOT(parent) AS ROOT,sys_connect_by_path(child,',') as path
                FROM bom1 b
                CONNECT BY PRIOR CHILD = PARENT
               START WITH PARENT IN ('a','a1')
               )
SELECT ROOT,CHILD,SUM(C_QTY) FROM (
        SELECT ROOT
              ,CHILD
              ,(SELECT POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
                  FROM vw_bom
               CONNECT BY PRIOR PATH=path||','||PRIOR CHILD AND ROOT = inner.ROOT
                 START WITH PATH = inner.PATH     -- 从每个叶子开始
                            --AND PARENT = inner.PARENT    -- 解决叶子（原料）不唯一
                            AND ROOT = inner.ROOT
                ) AS C_QTY
        FROM vw_bom inner
       WHERE NOT EXISTS (SELECT 1 FROM vw_bom WHERE parent = inner.child)
)        
GROUP BY ROOT,CHILD

myoekk

newkid 发表于 2016-9-8 22:41
你这个已经是图不是树了，应该用路径去匹配：

           WITH bom1 AS( SELECT 'a' PARENT, 'b' CHIL ...

好强悍的大神，膜拜！
偶尔有些产品不是直接从原材料一步到位生产出来，而是分不同部件生产出来放仓库，然后根据需要组装成产品。不同部件又可能含有相同的组件，组件由原材料生产而来。之前一直找不到能准确得到原材料的使用量的SQL语句。
感谢大神指导！