BOM结构合并，递归查询到最底层物料信息

有个需求：在SQL Server 2008 R2环境下，要将BOM结构进行合并，删除所有的中间节点，只保留最后一层的叶节点，同时要求将所有中间结点的数量合计到叶结点中，举例BOM如下：




注意，同一个末端的下级零件可能会出现在多个子节点下。

现在的思路是，先将所有的叶子节点节点查询出来，然后通过叶子节点，用SQL CTE递归找出该叶子的树形结构，然后再进行数量计算，发现对于只在一个分支出现的叶子节点，处理起来还可以，但是如果该叶子结点出现在多个分支下，就不知道如何处理了。


现有代码：

WITH CTE(CODE, NAME, PCODE, PNAME, QTY, RIndex, SEQ)
AS
(
	SELECT Distinct CODE, NAME, PCODE, PNAME, CONVERT(FLOAT, PLMRELQTY), RowIndex, PLMSEQ
	FROM bomleafTempTable
	WHERE CODE = '34C04-1-3-(3)'
	--WHERE CODE IN (SELECT CODE FROM bomleafTempTable)
	UNION ALL
	SELECT P.CODE, P.NAME, P.PCODE, P.PNAME, CONVERT(FLOAT, P.PLMRELQTY), P.RowIndex, P.PLMSEQ
	FROM bomTempTable AS P
	INNER JOIN CTE AS C ON C.PCODE = P.CODE
	WHERE LEN(P.PCODE) > 0 
)
SELECT * FROM CTE

如果采用从顶层开始向下查找，由于所有节点都放在一起，处理起来也有点烦。

--由父项递归下级 

WITH CTE(CODE, PCODE, NAME, QTY)
AS
(
	-- 父项
	SELECT CODE, PCODE, NAME, CONVERT(FLOAT, PLMRELQTY)
	FROM bomTempTable
	--WHERE len(ltrim(rtrim(PCODE))) = 0
	WHERE CODE = '34C01-19-0'
	UNION ALL
	--递归结果集中的下级
	SELECT C.CODE, C.PCODE, C.NAME, CONVERT(FLOAT, C.PLMRELQTY)
	FROM bomTempTable AS C
	INNER JOIN  CTE AS P ON C.PCODE = P.CODE 
)
SELECT * FROM CTE

请高手帮忙！谢谢。

5 个解决方案

#1

数据有EXCEL文件，但不知道在什么地方传附件！

#2

只能把数据导成制表符分割的TXT了：

34C01-19-0 34C01-0 支架 1
34C01-19-(1) 34C01-19-0 垫板t=12,300X400 4
34C01-19-2-0 34C01-19-0 支腿 2
34C01-19-3-0 34C01-19-0 支腿 2
34C01-19-4 34C01-19-0 槽钢[25a 1
34C01-19-5 34C01-19-0 槽钢[25a 1
34C01-19-6 34C01-19-0 槽钢[12 2
34C01-19-7 34C01-19-0 槽钢[12 2
34C01-19-8 34C01-19-0 垫板t=12 2
34C01-19-(9) 34C01-19-0 筋板t=8 8
34C01-19-(10) 34C01-19-0 筋板t=8 4
GB/T5781 34C01-19-0 螺栓M16X50 8
GB/T41 34C01-19-0 螺母M16 8
GB/T853 34C01-19-0 垫圈16 8
34C01-19-14-0 34C01-19-0 水泵支座 1
34C01-19-14-(1) 34C01-19-14-0 钢板t=6 2
34C01-19-14-(2) 34C01-19-14-0 钢板t=10,130X210 1
34C01-19-3-(1) 34C01-19-3-0 槽钢[18 2
34C01-19-3-(2) 34C01-19-3-0 槽钢[18 2
34C01-19-2-(1) 34C01-19-2-0 槽钢[18 2
34C01-19-2-(2) 34C01-19-2-0 槽钢[18 2

#3

类似的我做过，我是根据bom查出产品对应下所有的零部件包括原材料的用量及对应的人工工时、设备工时等，比你这个还稍微复杂点，树查询我也专门研究过 无非就是把握父节点及对应层级

#4

@jamjay2
具体代码呢，有没有参考的？

#5

现归结为如下内容的计算（只能用SQL）：



即根据CODE、QTY、Depth及RIndex算出某一零件在一部件或产品下的数量。