递归CTE中UNION ALL阶段过滤ROW_NUMBER()=1行的实现方法

这是解决递归层级中每一层都只取物料最新版本BOM行的正确方案，比仅在CTE外部过滤靠谱得多。

原因很简单：如果不在递归迭代时过滤旧版本，旧的BOM行已经关联了对应的子物料，这些错误的关联会被带入下一层递归，最终整个物料层级链都会出错——哪怕最后在外层过滤rn2=1，那些已经生成的错误子件数据也没法消除。

修改后的简化SQL示例 #

WITH BomExplosion AS (
    -- 锚点成员：获取第一层级的最新版本BOM行
    SELECT 
        parent_mat = Matnr,
        child_mat = Idnrk,
        quantity = Menge,
        level = 1
    FROM (
        SELECT 
            Matnr,
            Idnrk,
            Menge,
            -- 按物料分组，取版本最高的行
            ROW_NUMBER() OVER (PARTITION BY Matnr ORDER BY Version DESC) AS rn
        FROM Bom
    ) t
    WHERE rn = 1

    UNION ALL

    -- 递归成员：每一层都先筛选当前父物料的最新版本BOM行
    SELECT 
        parent_mat = be.child_mat,
        child_mat = b.Idnrk,
        quantity = b.Menge,
        level = be.level + 1
    FROM (
        SELECT 
            Matnr,
            Idnrk,
            Menge,
            -- 同样按物料分组取最新版本
            ROW_NUMBER() OVER (PARTITION BY Matnr ORDER BY Version DESC) AS rn2
        FROM Bom
    ) b
    JOIN BomExplosion be ON b.Matnr = be.child_mat
    WHERE b.rn2 = 1 -- 关键：在这里过滤，只让最新版本进入下一层递归
)
SELECT * FROM BomExplosion;

核心逻辑说明 #

在递归成员里，先通过子查询给当前要处理的所有BOM行加上行号rn2，按物料分组并按版本降序排序，然后只保留rn2=1的行（也就是最新版本）再去关联递归CTE的上一层数据。这样每一次迭代都只会用最新版本的BOM行去展开子物料，从根源上避免了旧版本数据污染整个递归链。

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