SQL Server递归BOM查询结果乱序、无子件零件不返回问题

两个异常的触发原因非常明确：

• 顺序错乱：递归CTE本身不会默认保证返回结果的排序，现有逻辑没有记录节点层级、也没有生成全局排序标识，最终返回顺序由数据库递归遍历的随机顺序决定，不符合BOM按层级、同层级按装配位置排序的展示要求。
• 无子件零件无返回：存储过程的锚点成员（递归的起始查询）加了[Parent] IS NULL的过滤条件，只有当入参零件存在「作为顶层产品、无父件」的记录时才能启动递归。测试数据中类似E、K、G这类仅作为子件存在、没有顶层无父件记录的零件，锚点查询直接返回空，整个递归结果自然为空，哪怕零件本身存在也不会返回。

修正逻辑如下：

1. 锚点直接构造入参零件的根节点记录，不依赖BOM表中是否存在该零件的顶层无父件记录，保证哪怕零件没有任何子件，也至少返回自身记录。
2. 递归过程中新增lvl（层级标识，根节点为0，每向下一层加1）、sort_path（排序路径，将每层节点的POSITION格式化为固定长度字符串拼接，实现全局有序）两个辅助字段，最终按排序路径返回结果。

修正后的存储过程代码如下（兼容SQL Server 2008及以上版本）：

USE [<Database>]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description: 获取指定零件的全层级BOM清单
-- =============================================
ALTER PROCEDURE GETBOM 
    @TL VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;

    WITH TESTBOM AS
    (
        -- 锚点成员：直接构造传入零件的根节点，不依赖BOM表的顶层记录
        SELECT 
            0 AS [POSITION],
            CAST(NULL AS VARCHAR(10)) AS [PARENT],
            @TL AS [PART],
            1 AS [QUANTITY],
            0 AS lvl,
            CAST(RIGHT('0000' + CAST(0 AS VARCHAR(4)), 4) AS VARCHAR(MAX)) AS sort_path
        UNION ALL
        -- 递归成员：逐层关联子件，记录层级和排序路径
        SELECT 
            E.[POSITION],
            E.[PARENT],
            E.[PART],
            E.[QUANTITY],
            M.lvl + 1 AS lvl,
            CAST(M.sort_path + '\' + RIGHT('0000' + CAST(E.[POSITION] AS VARCHAR(4)), 4) AS VARCHAR(MAX)) AS sort_path
        FROM 
            BOM E  
        INNER JOIN 
            TESTBOM M ON E.[PARENT] = M.[PART] 
    )
    -- 按排序路径返回，lvl字段可用于前端做层级缩进展示
    SELECT [POSITION], [PARENT], [PART], [QUANTITY], lvl
    FROM TESTBOM
    ORDER BY sort_path
END
GO

执行EXEC GETBOM '入参零件号'即可获取对应BOM：

• 入参为E/K/G/J这类没有下属子件的零件时，会直接返回该零件的根节点记录，不再出现空结果的问题。
• 所有返回结果严格按照「根节点→一级子件（按POSITION从小到大排列）→二级子件（同层级按POSITION从小到大排列）」的顺序输出，返回的lvl字段可直接用于前端做层级缩进展示，完全符合BOM的常规展示预期。

Access本身不支持SQL Server的CTE递归语法，无法直接写递归查询实现相同效果，需要通过两种方式适配：

• 编写VBA递归函数，遍历BOM表记录集，手动收集每个节点的子件、记录层级和排序值，最终整理为有序BOM结果。
• 借助临时表，先插入根节点记录，然后循环将子件插入临时表，直到没有新的子件记录可插入，最后按排序字段返回结果，逻辑和SQL Server的CTE递归完全一致。

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