U8全阶BOM展开sql

DECLARE @level INT 
SET @level= 1;  
WITH BomTree AS
(
SELECT bom.Version,c.ParentId AS TParentId,g.cinvcode as 母件编码,f.cinvcode AS 子件编码,f.bSelf,c.bomid,a.OpComponentId,a.ComponentId,b.wiptype,c.ParentId,0 AS LevelT ,bom.Status
FROM bom_bom bom 
LEFT JOIN bom_opcomponent a ON a.BomId = bom.BomId
LEFT JOIN bom_opcomponentopt b ON a.OptionsId=b.OptionsId
LEFT JOIN bom_parent c ON a.BomId=c.BomId
LEFT JOIN bas_part d ON a.componentid=d.partid  ---物料表跟子件关联
LEFT JOIN bas_part  e ON c.parentid=e.partid    ---物料表跟母件关联
LEFT JOIN inventory f ON d.invcode=f.cinvcode   ---子件
LEFT JOIN inventory g ON e.invcode=g.cinvcode   ---母件
WHERE 1=1 AND g.cInvCode='01003'
UNION ALL 
SELECT BomTree.Version,BomTree.ParentId AS TParentId,t1.母件编码,t1.子件编码,t1.bSelf,t1.bomid,t1.OpComponentId,t1.ComponentId,t1.wiptype,t1.ParentId,BomTree.LevelT + 1 as LevelT ,t1.Status
FROM BomTree
INNER JOIN (SELECT c.bomid,a.OpComponentId,a.ComponentId,b.wiptype,c.ParentId 
,g.cinvcode as 母件编码,f.cinvcode AS 子件编码,f.bSelf ,a1.Status
FROM bom_bom a1 INNER JOIN 
bom_opcomponent a ON a1.BomId = a.BomId
INNER JOIN bom_opcomponentopt b ON a.OptionsId=b.OptionsId
INNER JOIN bom_parent c ON a.BomId=c.BomId
INNER JOIN bas_part d ON a.componentid=d.partid  ---物料表跟子件关联
INNER JOIN bas_part  e ON c.parentid=e.partid    ---物料表跟母件关联
INNER JOIN inventory f ON d.invcode=f.cinvcode   ---子件
INNER JOIN inventory g ON e.invcode=g.cinvcode   ---母件
WHERE 1=1 
) t1 
ON  BomTree.ComponentId = t1.ParentId AND BomTree.LevelT<6 
)
SELECT *  FROM BomTree  ORDER BY LevelT