BOM嵌套简单写法

WITH
TREE AS(
SELECT a.FItemID cfitemid,b.FItemID pfitemid FROM dbo.ICBOMChild a,dbo.ICBOM b WHERE a.FInterID = b.FInterID
and a.FInterID = 1320 -- 要查询的子 id
UNION ALL
SELECT a.FItemID,b.FItemID FROM ICBOMChild a, dbo.ICBOM b,TREE
WHERE TREE.pfitemid = a.FItemID AND a.FInterID = b.FInterID
)
SELECT cfitemid,pfitemid FROM TREE

SELECT *FROM ICBOMChild WHERE FItemID = 76431

WITH
TREE AS(
SELECT a.FItemID cfitemid,b.FItemID pfitemid FROM dbo.ICBOMChild a,dbo.ICBOM b WHERE a.FInterID = b.FInterID
and a.FInterID = 1320 -- 要查询的父 id
UNION ALL
SELECT a.FItemID,b.FItemID FROM ICBOMChild a, dbo.ICBOM b,TREE
WHERE TREE.cfitemid = b.FItemID AND a.FInterID = b.FInterID
)
SELECT cfitemid,pfitemid FROM TREE