【金蝶K3Cloud】 mssql CTE 递归BOM

WITH CET(NewLev,LEV,FID, BOM版本, 父项物料ID,父项物料名称,子项物料ID,子项物料名称)
as (
SELECT  Cast( Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) as int) *100000 as NewLev, Cast( Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) as varchar(max)) AS LEV,T.FID,T.FNUMBER 'BOM版本',T.FMATERIALID AS '父项物料ID',T3.FNAME AS '父项物料名称',T1.FMATERIALID AS '子项物料ID',T2.FNAME AS '子项物料名称' FROM T_ENG_BOM T
JOIN T_ENG_BOMCHILD T1 ON T.FID = T1.FID
LEFT JOIN T_BD_MATERIAL_L T2 ON T1.FMATERIALID = T2.FMATERIALID AND T2.FLOCALEID = 2052
LEFT JOIN T_BD_MATERIAL_L T3 ON T.FMATERIALID = T3.FMATERIALID AND T3.FLOCALEID = 2052 
WHERE T.FMATERIALID = '239987' AND T.FDOCUMENTSTATUS = 'C' AND T.FFORBIDSTATUS <> 'B'
UNION ALL
SELECT CAST( cast(T.NewLev as varchar(max))+Cast( Row_Number() OVER (ORDER BY T1.子项物料ID DESC) as int)*100 AS int) as NewLev, CAST( T.LEV+'.'+Cast( Row_Number() OVER (ORDER BY T1.子项物料ID DESC) as varchar(max)) AS varchar(MAX)) AS LEV,T1.FID,T1.BOM版本,T1.父项物料ID,T1.父项物料名称,T1.子项物料ID,T1.子项物料名称 
FROM CET T 
JOIN (
SELECT CAST('1' AS varchar(MAX)) AS LEV,T.FID,T.FNUMBER 'BOM版本',T.FMATERIALID AS '父项物料ID',T3.FNAME AS '父项物料名称',T1.FMATERIALID AS '子项物料ID',T2.FNAME AS '子项物料名称',T.FFORBIDSTATUS,T.FDOCUMENTSTATUS FROM T_ENG_BOM T
JOIN T_ENG_BOMCHILD T1 ON T.FID = T1.FID
JOIN T_BD_MATERIAL_L T2 ON T1.FMATERIALID = T2.FMATERIALID AND T2.FLOCALEID = 2052
JOIN T_BD_MATERIAL_L T3 ON T.FMATERIALID = T3.FMATERIALID AND T3.FLOCALEID = 2052 
) T1 ON T.子项物料ID = T1.父项物料ID AND T1.FFORBIDSTATUS <> 'B'  AND T1.FDOCUMENTSTATUS = 'C'
)
select * from CET t 
order by t.NewLev

CTE 第一个SQL是递归的起点。

20181114更新 

上面的代码会有排序的问题。修改为下面代码

WITH CET(NewLev,LEV,FID, BOM版本, 父项物料ID,父项物料名称,子项物料ID,子项物料名称)
as (
SELECT  Cast( case when  Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) >= 1 and Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) < 10 then
'000'+cast(Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) >=10 and Row_Number() OVER (ORDER BY T1.FMATERIALID DESC)<100 then 
'00'+cast(Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) >= 100 and Row_Number() OVER (ORDER BY T1.FMATERIALID DESC)<1000 then 
'0'+cast(Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) as varchar(max)) end as varchar(max) ) as NewLev
, Cast( Row_Number() OVER (ORDER BY T1.FMATERIALID DESC) as varchar(max)) AS LEV,
T.FID,T.FNUMBER 'BOM版本',T.FMATERIALID AS '父项物料ID',T3.FNAME AS '父项物料名称',T1.FMATERIALID AS '子项物料ID',T2.FNAME AS '子项物料名称' FROM T_ENG_BOM T
JOIN T_ENG_BOMCHILD T1 ON T.FID = T1.FID
LEFT JOIN T_BD_MATERIAL_L T2 ON T1.FMATERIALID = T2.FMATERIALID AND T2.FLOCALEID = 2052
LEFT JOIN T_BD_MATERIAL_L T3 ON T.FMATERIALID = T3.FMATERIALID AND T3.FLOCALEID = 2052 
WHERE T.FMATERIALID = '239850' AND T.FDOCUMENTSTATUS = 'C' AND T.FFORBIDSTATUS <> 'B'
UNION ALL
SELECT 
cast(T.NewLev as varchar(max))+ Cast( case when  Row_Number() OVER (ORDER BY T1.子项物料ID DESC) >= 1 and Row_Number() OVER (ORDER BY T1.子项物料ID DESC) < 10 then
'000'+ cast(Row_Number() OVER (ORDER BY T1.子项物料ID DESC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.子项物料ID DESC) >=10 and Row_Number() OVER (ORDER BY T1.子项物料ID DESC)<100 then 
'00'+ cast(Row_Number() OVER (ORDER BY T1.子项物料ID DESC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.子项物料ID DESC) >= 100 and Row_Number() OVER (ORDER BY T1.子项物料ID DESC)< 1000 then 
'0'+ cast(Row_Number() OVER (ORDER BY T1.子项物料ID DESC)  as varchar(max)) end  as varchar(max)
 )  as NewLev,
CAST( T.LEV+'.'+Cast( Row_Number() OVER (ORDER BY T1.子项物料ID DESC) as varchar(max)) AS varchar(MAX)) AS LEV,
T1.FID,T1.BOM版本,T1.父项物料ID,T1.父项物料名称,T1.子项物料ID,T1.子项物料名称 
FROM CET T 
JOIN (
SELECT CAST('1' AS varchar(MAX)) AS LEV,T.FID,T.FNUMBER 'BOM版本',T.FMATERIALID AS '父项物料ID',T3.FNAME AS '父项物料名称',T1.FMATERIALID AS '子项物料ID',T2.FNAME AS '子项物料名称',T.FFORBIDSTATUS,T.FDOCUMENTSTATUS FROM T_ENG_BOM T
JOIN T_ENG_BOMCHILD T1 ON T.FID = T1.FID
JOIN T_BD_MATERIAL_L T2 ON T1.FMATERIALID = T2.FMATERIALID AND T2.FLOCALEID = 2052
JOIN T_BD_MATERIAL_L T3 ON T.FMATERIALID = T3.FMATERIALID AND T3.FLOCALEID = 2052 
) T1 ON T.子项物料ID = T1.父项物料ID AND T1.FFORBIDSTATUS <> 'B'  AND T1.FDOCUMENTSTATUS = 'C'
)
select * from CET t  
order by t.NewLev

效果图

20181127更新，排序随意使用了子项物料ID，导致和客户前台输入的顺序不一致，更新排序为物料清单的序号。从此跟标准产品物料清单正差数据一致。

WITH CET(NewLev,LEV,FID,FREPLACEGROUP, BOM版本, 父项物料ID,父项物料名称,子项物料ID,子项物料名称)
as (
SELECT  Cast( case when  Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) >= 1 and Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) < 10 then
'000'+cast(Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) >=10 and Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC)<100 then 
'00'+cast(Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) >= 100 and Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC)<1000 then 
'0'+cast(Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) as varchar(max)) end as varchar(max) ) as NewLev
, Cast( Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) as varchar(max)) AS LEV,
T.FID,T1.FREPLACEGROUP,T.FNUMBER 'BOM版本',T.FMATERIALID AS '父项物料ID',T3.FNAME AS '父项物料名称',T1.FMATERIALID AS '子项物料ID',T2.FNAME AS '子项物料名称' FROM T_ENG_BOM T
JOIN T_ENG_BOMCHILD T1 ON T.FID = T1.FID
LEFT JOIN T_BD_MATERIAL_L T2 ON T1.FMATERIALID = T2.FMATERIALID AND T2.FLOCALEID = 2052
LEFT JOIN T_BD_MATERIAL_L T3 ON T.FMATERIALID = T3.FMATERIALID AND T3.FLOCALEID = 2052 
WHERE T.FMATERIALID = @FMaterialId AND T.FDOCUMENTSTATUS = 'C' AND T.FFORBIDSTATUS <> 'B'
UNION ALL
SELECT 
cast(T.NewLev as varchar(max))+ Cast( case when  Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) >= 1 and Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) < 10 then
'000'+ cast(Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) >=10 and Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC)<100 then 
'00'+ cast(Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) as varchar(max))
when Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) >= 100 and Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC)< 1000 then 
'0'+ cast(Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC)  as varchar(max)) end  as varchar(max)
 )  as NewLev,
CAST( T.LEV+'.'+Cast( Row_Number() OVER (ORDER BY T1.FREPLACEGROUP ASC) as varchar(max)) AS varchar(MAX)) AS LEV,
T1.FID,T1.FREPLACEGROUP,T1.BOM版本,T1.父项物料ID,T1.父项物料名称,T1.子项物料ID,T1.子项物料名称 
FROM CET T 
JOIN (
SELECT CAST('1' AS varchar(MAX)) AS LEV,T.FID,T.FNUMBER 'BOM版本',T.FMATERIALID AS '父项物料ID',T3.FNAME AS '父项物料名称',T1.FMATERIALID AS '子项物料ID',
T2.FNAME AS '子项物料名称',T.FFORBIDSTATUS,T.FDOCUMENTSTATUS,T1.FENTRYID,T1.FREPLACEGROUP
FROM T_ENG_BOM T
JOIN T_ENG_BOMCHILD T1 ON T.FID = T1.FID
JOIN T_BD_MATERIAL_L T2 ON T1.FMATERIALID = T2.FMATERIALID AND T2.FLOCALEID = 2052
JOIN T_BD_MATERIAL_L T3 ON T.FMATERIALID = T3.FMATERIALID AND T3.FLOCALEID = 2052 
) T1 ON T.子项物料ID = T1.父项物料ID AND T1.FFORBIDSTATUS <> 'B'  AND T1.FDOCUMENTSTATUS = 'C'
)
select t.*,d.FMATERIALID,t1.FNAME from CET t  
JOIN t_BD_MaterialBase d ON t.子项物料ID = d.FMATERIALID            ----查询出物料属性
left join T_BD_UNIT_L t1 on D.FBASEUNITID = T1.FUNITID and t1.FLOCALEID = 2052 -- 20181115 基本单位
order by t.NewLev