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