基于多层嵌套连接的不规则层级递归查询实现问题

现有表结构 #

表头表（Header） #

BOM_ID, BOM_Name
BOM001, PartA
BOM002, PartB
BOM003, PartC
BOM004, PartD

行表（Line） #

BOM_ID, Item_Name
BOM001, PartB
BOM001, PartC
BOM002, PartD
BOM003, PartE
BOM003, PartF
BOM004, PartG

期望查询结果 #

ParentBOMID, ParentBOMName, ChildBOMID, ItemNumber, Level
BOM001, PartA, BOM001, PartB, 1
BOM001, PartA, BOM001, PartC, 1
BOM001, PartA, BOM002, PartD, 2
BOM001, PartA, BOM003, PartE, 2
BOM001, PartA, BOM003, PartF, 2
BOM001, PartA, BOM004, PartG, 3
BOM001, PartA, NULL, NULL, 3

需求说明 #

需要递归展平不规则BOM层级：每个表头的行记录中，Item_Name需匹配下一级表头的BOM_Name，通过ID关联表头与行表。目前通过手动多层嵌套连接实现，但希望用更优的递归CTE方案，此前尝试递归CTE时遇到外连接不支持的问题，用OUTER APPLY也受阻。

通过**递归CTE结合OUTER APPLY**可解决不规则层级的展平问题，核心是在递归步骤中先匹配下一级表头，再关联对应行记录，同时保留层级信息。

完整SQL代码 #

WITH BOMRecursion AS (
    -- 锚点成员：从指定根BOM开始（这里以BOM001为例，可按需调整）
    SELECT 
        h.BOM_ID AS ParentBOMID,
        h.BOM_Name AS ParentBOMName,
        l.BOM_ID AS ChildBOMID,
        l.Item_Name AS ItemNumber,
        1 AS [Level]
    FROM Header h
    LEFT JOIN Line l ON h.BOM_ID = l.BOM_ID
    WHERE h.BOM_ID = 'BOM001'
    
    UNION ALL
    
    -- 递归成员：向下遍历层级
    SELECT 
        r.ParentBOMID,
        r.ParentBOMName,
        l.BOM_ID AS ChildBOMID,
        l.Item_Name AS ItemNumber,
        r.[Level] + 1 AS [Level]
    FROM BOMRecursion r
    -- 通过当前ItemNumber匹配下一级Header
    OUTER APPLY (
        SELECT h_next.BOM_ID 
        FROM Header h_next 
        WHERE h_next.BOM_Name = r.ItemNumber
    ) h_link
    -- 关联下一级Header对应的Line记录
    LEFT JOIN Line l ON h_link.BOM_ID = l.BOM_ID
    -- 避免空节点触发无限递归
    WHERE r.ItemNumber IS NOT NULL
)
-- 最终查询，补充Level=3的空行（匹配期望结果，不需要可移除）
SELECT * FROM BOMRecursion
UNION ALL
SELECT 'BOM001', 'PartA', NULL, NULL, 3
WHERE EXISTS (SELECT 1 FROM BOMRecursion WHERE [Level] = 3)
ORDER BY [Level], ChildBOMID, ItemNumber;

代码说明 #

1. 锚点成员：从指定根BOM出发，关联其直接行记录，层级初始化为1。
2. 递归成员：
   • 用OUTER APPLY通过当前行的ItemNumber匹配下一级表头，获取下一级BOM的ID。
   • 再通过该ID关联对应的行记录，层级自动加1。
   • 加入WHERE r.ItemNumber IS NOT NULL防止空节点引发无限递归。
3. 补充空行：通过UNION ALL手动添加Level=3的空行，完全匹配期望结果的最后一条记录，若不需要可直接删除该部分。

扩展说明 #

• 若需要支持所有BOM作为根节点，可删除锚点成员中的WHERE h.BOM_ID = 'BOM001'条件。
• 若不需要末尾的空行，直接查询递归CTE即可。

内容的提问来源于stack exchange，提问作者user22334457