CTE创建产品层次结构树

Question

我有以下三个表来表示产品数据。简单地解释一下，产品"A"one_answers"B"是最终产品。为了找出制造产品"A"的部件，我们查看"ProductComponents"表，该表给出ComponentListId=1。

根据"PartsSubcomponents"表查询此ComponentListId可以告诉我们它有两个子组件，即A11和A12。

A11是很小的，它没有更多的子组件。然而，A12存在于"ProductComponents"表中，告诉我们它是由X1和X2制成的。

最终产品

EndProductId
A
B
...

产品组件

ProductId ComponentListId
A         1
A12       99
...

部件子组件

ComponentListId SubComponentId
1               A11
1               A12
99              X1
99              X2
...

我需要使用CTE来查找产品及其部件之间的层次结构级别。在这种情况下，结果应该是这样的：

EndProductId,ProductId,ComponentListId,SubcomponentId,Level
A,           A,        1,              A11,           L1
A,           A,        1,              A12,           L1
A,           A12,      99,             X1,            L2
A,           A12,      99,             X2,            L2

Answer 1

这里有一个简单的递归cte，它可以执行您想要的操作并产生您想要的输出：

CREATE TABLE #EndProducts
(
EndProductId NVARCHAR(1)
);
INSERT  INTO #EndProducts
( EndProductId )
VALUES  ( 'A' ),
( 'B' );
CREATE TABLE #ProductComponents
(
ProductId NVARCHAR(3) ,
ComponentListId INT
);
INSERT  INTO #ProductComponents
( ProductId, ComponentListId )
VALUES  ( 'A', 1 ),
( 'A12', 99 );
CREATE TABLE #PartsSubcomponents
(
ComponentListId INT ,
SubComponentId NVARCHAR(3)
); 
INSERT  INTO #PartsSubcomponents
( ComponentListId, SubComponentId )
VALUES  ( 1, 'A11' ),
( 1, 'A12' ),
( 99, 'X1' ),
( 99, 'X2' );
WITH    cte
AS ( -- anchor member gets level 1
SELECT   e.EndProductId ,
pc.ProductId ,
sc.ComponentListId ,
sc.SubComponentId ,
1 AS [Level]
FROM     #EndProducts e
INNER JOIN #ProductComponents pc 
ON e.EndProductId = pc.ProductId
INNER JOIN #PartsSubcomponents sc 
ON pc.ComponentListId = sc.ComponentListId
UNION ALL
-- recursive member gets the additional data and increments levels
SELECT   cte.EndProductId ,
cte.SubComponentId AS ProductId ,
pc.ComponentListId ,
sc.SubComponentId ,
cte.[Level] + 1 AS [Level]
FROM     cte
INNER JOIN #ProductComponents pc 
ON cte.SubComponentId = pc.ProductId
INNER JOIN #PartsSubcomponents sc 
ON pc.ComponentListId = sc.ComponentListId
)
SELECT  *
FROM    cte;
DROP TABLE #EndProducts;
DROP TABLE #PartsSubcomponents;
DROP TABLE #ProductComponents;

结果：

EndProductId    ProductId   ComponentListId SubComponentId  Level
A               A           1               A11             1
A               A           1               A12             1
A               A12         99              X1              2
A               A12         99              X2              2