主题：CTE中如何避免重复值

WITH CTE
    AS (SELECT a.产品编码,
            CAST(NULL AS NVARCHAR(20)) AS 父件编码,
            CAST(1 AS FLOAT) AS 用量,
            CAST(0 AS FLOAT) AS 损耗,
            0 AS Levle,
            CAST(产品编码 AS VARCHAR(MAX)) AS path
        FROM (SELECT DISTINCT 产品编码 FROM MPS01) a
        UNION ALL
        SELECT BOM.产品编码,
            BOM.父件编码,
            BOM.用量,
            BOM.损耗,
            c.Levle + 1 AS Levle,
            CAST(c.path + '\' + BOM.产品编码 AS VARCHAR(MAX)) AS path
        FROM CTE AS c
            INNER JOIN BOM
                ON c.产品编码 = BOM.父件编码
       )

select * from (SELECT *,ROW_NUMBER() over(partition by 产品编码,父件编码 order by levle desc) as rowNum from cte) as a where rowNum = 1

无法做到你说的去重复。