CREATE TABLE BOM(阀门ID VARCHAR(10),零件ID VARCHAR(10),单位用量 INT)
GO
INSERT BOM SELECT 
'a','a1', 1  UNION ALL SELECT 
'a1','a11', 1 UNION ALL SELECT 
'a' ,'a2', 1 UNION ALL SELECT 
'a2', 'a22' ,1 UNION ALL SELECT 
'b', 'b1' ,1 UNION ALL SELECT 
'b1', 'b11' ,1 UNION ALL SELECT 
'b', 'b2', 1 UNION ALL SELECT 
'b2', 'b22' ,1;
GO 
;WITH cte AS
(
    SELECT * ,path=CAST(阀门ID+'->'+零件ID AS VARCHAR(8000)),lev=1 FROM BOM WHERE patindex('%[1-9]%',阀门ID)=0
    UNION ALL
    SELECT B.*,cast(c.path+'->'+B.零件ID AS VARCHAR(8000)),LEV+1 FROM CTE C JOIN BOM B ON C.零件ID=B.阀门ID
)
SELECT LEFT(c.path,charindex('-',c.path)-1)  阀门ID,零件ID,单位用量 
FROM CTE c 
WHERE NOT EXISTS (SELECT * FROM cte WHERE  LEFT(path,charindex('-',path)-1)=LEFT(c.path,charindex('-',c.path)-1) AND LEV>c.lev)
ORDER BY 阀门ID,零件ID
/*
阀门ID 零件ID 单位用量
------ ------ --------
a      a11           1
a      a22           1
b      b11           1
b      b22           1*/
DROP TABLE bom