WITH CTE
AS (SE LECT 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 (SE LECT DISTINCT 产品编码 FROM MPS01) a
UNION ALL
SE LECT 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.父件编码
WHERE NOT EXISTS (SE LECT * FROM CTE WHERE CTE.产品编码 = bom.产品编码 and CTE.父件编码 = bom.父件编码)
)
上述where条件的作用就是每次循环时产品编码及父件编码相同的值就不要加入进去,因为CTE不能第二次引用 ,要如何写才能实现这个功能?
谢谢!
加好友 
发短信
Post By：2018/4/3 19:41:00 [只看该作者]
