金蝶K3Cloud MSSQL CTE BOM反查

 1 DROP TABLE JZC_MATERIALIDS
 2 CREATE TABLE JZC_MATERIALIDS( FMaterialId INT)
 3 INSERT    JZC_MATERIALIDS(FMaterialId)VALUES(316344)
 4 
 5 SELECT * FROM JZC_MATERIALIDS
 6 SELECT * FROM dbo.T_BD_MATERIAL WHERE FNUMBER = 'CL001'
 7 
 8 WITH CTE AS(
 9     SELECT 
10         FMaterialId,FMaterialId AS 'FFMaterialId',1 AS FBomLev
11         ,CAST('' AS NVARCHAR(MAX)) AS FBomVerison,0 AS FBomId,0 AS FBomEntryId
12         ,NEWID() AS 'FRowId',NEWID() AS 'FParentRowId'
13     FROM JZC_MATERIALIDS    
14     UNION ALL
15     SELECT 
16         T1.FMATERIALID,T2.FMATERIALID AS 'FFMaterialId',T.FBomLev+1
17         ,CAST(T2.FNUMBER AS NVARCHAR(MAX)),T2.FID,T1.FENTRYID
18         ,NEWID() AS 'FRowId',T.FRowId AS 'FParentRowId'
19     FROM CTE T
20         JOIN dbo.T_ENG_BOMCHILD T1 ON T.FFMaterialId = T1.FMATERIALID
21         JOIN dbo.T_ENG_BOM T2 ON T1.FID = T2.FID
22 )
23 SELECT T1.FNUMBER AS '子项物料编码',T2.FNUMBER AS '父项物料编码',T.* 
24 FROM CTE T
25     JOIN dbo.T_BD_MATERIAL T1 ON T.FMaterialId = T1.FMATERIALID
26     JOIN dbo.T_BD_MATERIAL T2 ON T.FFMaterialId = T2.FMATERIALID