SQL递归CTE“在哪里使用"/BOM爆炸 [英] SQL Recursive CTE 'where-used' / BOM explosion
问题描述
我想做的只是从BOM表(物料清单)中获得一个物料，然后从BOMVERSION中获得其相关的组件/产品，然后对于那些相关的组件/产品，将其相关的组件和产品等还原到7个级别.然后，我想透视结果，以便将相关项目放在0, 1, 2, 3, 4, 5, 6, 7
What I'm trying to do simply is for an item from the BOM table (Bill of Materials) get it's related components/products from BOMVERSION and then for those related components/products get their related components and products etc. down to 7 levels. I then want to pivot the results so that the related items get put in columns 0, 1, 2, 3, 4, 5, 6, 7
请参阅随附的示例数据和代码.我使用的是Dynamics AX 2012 R2，但此示例可以应用于使用材料/产品的任何系统.但是，我无法完成查询(我知道我没有为简单起见而遗漏的dataareaid和分区).该关系是BOM表中的itemid，通过BOMID与BOMVERSION中的itemid相关.
Please see attached example data and code. I'm using dynamics AX 2012 R2 but this example could be applied to any system that uses materials/products. I cannot get my query to complete however (I know I don't have dataareaid and partition I've left out for simplicity). the relationship is an itemid from BOM table is related to itemid in BOMVERSION through BOMID.
更新:我已经简化了数据等，以使其尽可能简单/清晰，因此，木材，金属，玻璃材料可以用来制造各种产品和材料，并且可以组合在一起来制造产品或材料.因此，我想从基础组件开始，然后按级别展开关系.
UPDATE : I've simplified data etc. to make as simple/clear as possible, so materials wood, metal, glass can go into making various products and materials themselves, and can be combined to make products or materials. Therefore I want to start with a base component, then explode the relationships out by levels.
DDL + DML:
DDL + DML:
USE tempdb;
IF OBJECT_ID('tempdb..#BOM') IS NOT NULL
DROP TABLE #BOM;
CREATE TABLE #BOM
(
ITEMID NVARCHAR(10) ,
BOMID NVARCHAR(10) ,
MATERIALNAME NVARCHAR(10)
);
INSERT INTO #BOM
VALUES ( N'113621', -- ITEMID - nvarchar(10)
N'1', -- BOMID - nvarchar(10)
N'Wood' -- MATERIALNAME - nvarchar(10)
);
INSERT INTO #BOM
VALUES ( N'234517', -- ITEMID - nvarchar(10)
N'2', -- BOMID - nvarchar(10)
N'Metal' -- MATERIALNAME - nvarchar(10)
);
INSERT INTO #BOM
VALUES ( N'378654', -- ITEMID - nvarchar(10)
N'3', -- BOMID - nvarchar(10)
N'Glass' -- MATERIALNAME - nvarchar(10)
);
IF OBJECT_ID('tempdb..#BOMVERSION') IS NOT NULL
DROP TABLE #BOMVERSION;
CREATE TABLE #BOMVERSION
(
ITEMID NVARCHAR(10) ,
BOMID NVARCHAR(10) ,
NAME NVARCHAR(20)
);
INSERT INTO #BOMVERSION
VALUES ( N'113477', -- ITEMID - nvarchar(10)
N'1', -- BOMID - nvarchar(10)
N'Oak' -- NAME - nvarchar(10)
);
INSERT INTO #BOMVERSION
VALUES ( N'113608', -- ITEMID - nvarchar(10)
N'1', -- BOMID - nvarchar(10)
N'Pine' -- NAME - nvarchar(10)
);
INSERT INTO #BOMVERSION
VALUES ( N'113622', -- ITEMID - nvarchar(10)
N'1', -- BOMID - nvarchar(10)
N'Wood Table' -- NAME - nvarchar(10)
);
INSERT INTO #BOMVERSION
VALUES ( N'113683', -- ITEMID - nvarchar(10)
N'2', -- BOMID - nvarchar(10)
N'Aluminium' -- NAME - nvarchar(10)
);
INSERT INTO #BOMVERSION
VALUES ( N'113689', -- ITEMID - nvarchar(10)
N'2', -- BOMID - nvarchar(10)
N'Steel' -- NAME - nvarchar(10)
);
INSERT INTO #BOMVERSION
VALUES ( N'113693', -- ITEMID - nvarchar(10)
N'2', -- BOMID - nvarchar(10)
N'Metal table' -- NAME - nvarchar(10)
);
INSERT INTO #BOMVERSION
VALUES ( N'113694', -- ITEMID - nvarchar(10)
N'3', -- BOMID - nvarchar(10)
N'Glass' -- NAME - nvarchar(10)
);
INSERT INTO #BOMVERSION
VALUES ( N'113695', -- ITEMID - nvarchar(10)
N'3', -- BOMID - nvarchar(10)
N'Glass BookCase' -- NAME - nvarchar(10)
);
--Query
WITH BOM1
AS ( SELECT B.ITEMID AS BITEMID ,
BV.ITEMID AS BVITEMID ,
B.MATERIALNAME ,
B.BOMID
FROM #BOM AS B
JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
),
EXPLODE
AS ( SELECT B.BITEMID ,
B.MATERIALNAME ,
B.BVITEMID ,
B.BOMID ,
0 AS [Level]
FROM BOM1 AS B
UNION ALL
SELECT B.BITEMID ,
E.MATERIALNAME ,
E.BVITEMID ,
E.BOMID ,
[E].[Level] + 1
FROM EXPLODE AS E
JOIN BOM1 AS B ON B.BOMID = E.BOMID
WHERE E.Level <= 6 --narrowing levels
)
SELECT *
FROM EXPLODE PIVOT ( MAX(BVITEMID) FOR Level IN ( [0], [1], [2], [3],
[4], [5], [6], [7] ) ) AS PVTBOM;
输出看起来像
推荐答案
这只是为了消除错误并获得结果，但是我不知道结果是否正确:
Well this is just to eliminate errors and get results, but I don't know if the results are correct:
WITH BOM1
AS ( SELECT B.ITEMID AS BITEMID ,
BV.ITEMID AS BVITEMID ,
B.BOMID
FROM #BOM AS B
JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
),
EXPLODE
AS ( SELECT B.BITEMID ,
B.BVITEMID ,
B.BOMID ,
0 AS [Level]
FROM BOM1 AS B
UNION ALL
SELECT B.BITEMID ,
E.BVITEMID ,
E.BOMID ,
[E].[Level] + 1
FROM EXPLODE AS E
JOIN BOM1 AS B ON B.BOMID = E.BOMID
WHERE e.Level <= 6 --narrowing levels
)
SELECT *
FROM EXPLODE PIVOT ( MAX(BVITEMID) FOR Level IN ( [0], [1], [2], [3],
[4], [5], [6], [7] ) ) AS PVTBOM
您有maxrecursion = 7-此处不是缩小级别的地方.在CTE中，递归部分的级别变窄.
You had maxrecursion = 7 - this is not the place to narrow the levels. Levels are narrowed inside recursive part in CTE.
我怀疑您的递归组织得不好，那么您可以简化测试数据吗?只需留下几行并显示预期的输出即可.
I suspect your recursion is not well organized, so can you simplify your test data? Just leave several rows and show the expected output.
WITH p AS ( SELECT *
FROM ( SELECT itemid ,
bomid ,
ROW_NUMBER() OVER ( PARTITION BY Bomid ORDER BY itemid ) rn
FROM #BOMVERSION
) t PIVOT ( MAX(itemid) FOR rn IN ( [1], [2], [3], [4],
[5], [6], [7], [8] ) ) p
)
SELECT *
FROM #bom b
JOIN p ON b.bomid = p.bomid
;WITH cte
AS ( SELECT b.itemid AS originalitem ,
b.bomid AS originalbom ,
b.bomid ,
bv.itemid AS parent ,
0 AS level
FROM #BOM b
JOIN #BOMVERSION bv ON bv.bomid = b.bomid
UNION ALL
SELECT c.originalitem ,
c.originalbom ,
b.bomid ,
bv.itemid ,
c.level + 1
FROM cte c
JOIN #BOM b ON c.parent = b.itemid
JOIN #BOMVERSION bv ON bv.bomid = b.bomid
WHERE c.level <= 6
),
tree
AS ( SELECT originalitem ,
originalbom ,
parent ,
level
FROM cte
)
SELECT *
FROM tree PIVOT ( MAX(parent) FOR level IN ( [0], [1], [2], [3], [4], [5], [6], [7] ) ) AS p
这篇关于SQL递归CTE“在哪里使用"/BOM爆炸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
