sql - 我如何从 Material list 表中获取递归数据

Question

这是在 SQL Server 2008 中。

我计划创建两个表来表示 Material list 。 Item Master 的数据如下:-

[ID] [项目代码] [项目描述]

1---- A001 ---- 组装1
2---- B001 ---- Sub Assembly 1 (Child of Assembly 1)
3---- B002 ---- Sub Assembly 2 (Child of Assembly 1)
4---- C001---- 组件 1(子组件 1 的子组件)
5---- C002---- 组件 2(子组件 2 的子组件)

BOM 关系表将有如下数据。 [Parent Item ID] 和 [Child Item ID] 是 Item master 的外键。 :-

[ID] [父项 ID] [子项 ID]

1---- 1---- 2
2---- 1---- 3
3---- 2---- 4
4---- 3---- 5

所以第一个表只有项目本身，另一个表有父 ID 和子 ID 之间的关系。

考虑到它可能必须根据添加到上述表中的数据进行递归迭代，为程序集 (A001) 获取所有子项的 SQL 可能是什么？

所以对于上面的数据，我应该得到如下输出:-

1) A001

                1.1) B001       
                               1.1.1)C001      
                1.2) B002         
                               1.2.1) C002  

谢谢，泽。

Answer 1

使用 recursive CTE .例如:

WITH BomTree (ID, Level, [Item Code], [Item Description], Depth)
AS
(
-- Anchor member definition
    SELECT m.*, 0 AS Depth
    FROM dbo.Master AS m
    WHERE m.[ID] = ?
    UNION ALL
-- Recursive member definition
    SELECT m.*, t.Depth + 1 AS Depth
    FROM dbo.Master AS m
    INNER JOIN dbo.BOM AS b
        ON m.[ID] = b.[Child Item ID]
    INNER JOIN BomTree AS t
        ON b.[Parent Item ID] = t.ID
)
-- Statement that executes the CTE
SELECT * FROM BomTree;