如何获取完整BOM？现有SQL递归查询仅返回前2层级的优化求助

要获取完整的BOM层级，核心是使用递归CTE（公共表表达式），它由锚点成员（起始层级）和递归成员（循环关联子层级）两部分组成，能自动遍历所有嵌套层级。以下是针对你问题的具体解决思路和代码：

核心思路 #

1. 先通过join_table关联所有需要的表，避免重复写JOIN逻辑；
2. 锚点成员：定义BOM的起始层级（即主产品的第一层子件）；
3. 递归成员：通过自身关联，将当前层级的子件作为父件，循环获取下一层子件，直到没有子件为止；
4. 加入层级标记和路径跟踪，方便排查层级关系，同时避免循环BOM导致的无限递归。

修正后的完整代码 #

WITH join_table AS (
    -- 明确指定需要的字段，避免多表同名字段冲突，不要用SELECT *
    SELECT 
        A.Article, 
        A.Bom_Composant,
        -- 替换为你实际需要的其他字段
        B.Field1, 
        C.Field2, 
        D.Field3, 
        E.Field4, 
        F.Field5
    FROM A
    INNER JOIN B ON A.B_ID = B.ID  -- 替换为你的实际关联条件
    INNER JOIN C ON A.C_ID = C.ID
    INNER JOIN D ON B.D_ID = D.ID
    INNER JOIN E ON C.E_ID = E.ID
    INNER JOIN F ON D.F_ID = F.ID
    WHERE cond2 AND cond3  -- 通用筛选条件
),
RecursiveBOM AS (
    -- 锚点成员：主产品的第一层BOM
    SELECT 
        jt.*,
        1 AS BOM_Level,  -- 标记当前层级
        CAST(jt.Article AS VARCHAR(MAX)) AS BOM_Path  -- 跟踪BOM路径，用于避免循环
    FROM join_table jt
    WHERE cond1  -- 主产品的筛选条件

    UNION ALL

    -- 递归成员：获取当前子件的下一层BOM
    SELECT 
        jt.*,
        rb.BOM_Level + 1 AS BOM_Level,
        rb.BOM_Path + ' -> ' + CAST(jt.Article AS VARCHAR(MAX)) AS BOM_Path
    FROM join_table jt
    INNER JOIN RecursiveBOM rb 
        ON jt.Article = rb.Bom_Composant  -- 用当前层的子件作为父件，查询其子件
    -- 避免循环BOM（如A包含B，B包含A），防止无限递归
    WHERE CHARINDEX(CAST(jt.Article AS VARCHAR(MAX)), rb.BOM_Path) = 0
)
-- 输出所有层级的BOM，按层级和路径排序
SELECT * FROM RecursiveBOM
ORDER BY BOM_Level, BOM_Path
OPTION (MAXRECURSION 0);  -- 允许无限递归（默认SQL Server限制100层）

针对你原有问题的说明 #

1. 第一个查询的重复数据问题：你用UNION ALL只是合并了两个条件的结果集，并非递归逻辑，所以会出现重复，且无法遍历深层级。递归CTE的UNION ALL是锚点和递归成员的关联，会自动按层级遍历，不会无意义重复。
2. 第二个查询无法获取全层级问题：你只手动关联了两层（主BOM+子件BOM），没有递归循环逻辑。递归CTE会自动重复执行递归成员，直到没有新的子件数据返回。

注意事项 #

• 字段明确化：不要用SELECT *，避免多表同名字段冲突，同时提升查询性能；
• 循环BOM处理：如果你的BOM存在循环引用（如组件包含父件），BOM_Path和CHARINDEX的判断可以阻止无限递归；
• 索引优化：给Article和Bom_Composant字段添加索引，大幅提升递归查询的性能；
• 递归深度：OPTION (MAXRECURSION 0)允许无限递归，如果你能确定最大层级，也可以设置具体数值（如1000）。

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