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
