SQL：生成物料全名（按物料分组层级拼接物料名称）

一、前言

        如下图，有一些用户想看到物料的全名，像老K3wise一样。但是金蝶云没有物料全名的字段，所以只能通过二开生成一个全名字段。

二、SQL

---------------------------------------------------------------------------------------------------------

--20220829 查询物料全名

with cte1

as

(

select 0 as 物料分组层级,t1.fid,t1.FNUMBER,t2.FNAME,t1.FPARENTID,convert(varchar(max),t2.FNAME) as 分组名称--,convert(varchar(max),t1.FNUMBER) as fnumber

from T_BD_MATERIALGROUP t1

join T_BD_MATERIALGROUP_L t2

on t1.fid = t2.FID and t2.FLOCALEID = 2052

union all 

select c1.物料分组层级+1 as 当前层级,a1.fid,a1.FNUMBER,a2.FNAME,a1.FPARENTID,convert(varchar(max),c1.分组名称 +'_'+a2.FNAME) as a--,convert(varchar(max),wlb.FNUMBER) as fnumber

from cte1 c1

join T_BD_MATERIALGROUP a1

on  c1.FID = a1.FPARENTID 

join T_BD_MATERIALGROUP_L a2

on a1.fid = a2.FID and a2.FLOCALEID = 2052

)

,cte2

as

(

select wlb.FNUMBER as 物料编码,分组名称+'_'+wlbmc.FNAME as 物料全名,wlbmc.FSPECIFICATION as 规格型号

,c1.*,count(物料分组层级) over(partition by wlb.FNUMBER ) as materialCount

from cte1 c1

join T_BD_MATERIAL wlb

on  c1.FID = wlb.FMATERIALGROUP 

and wlb.FCREATEORGID = wlb.FUSEORGID

join T_BD_MATERIAL_L wlbmc

on wlbmc.FMATERIALID = wlb.FMATERIALID and wlbmc.FLOCALEID = 2052

--where c1.FNUMBER = 'a06.004.0001' or c1.FNUMBER like '3.b' 

)

select *

from cte2

where 物料分组层级 = materialCount-1 --取递归最底层的那个分组数据

order by 物料编码

---------------------------------------------------------------------------------------------------------
上述SQL的原件已经上传到附件中，社区直接粘贴的代码，格式会乱，下载附件查看更清晰！

相关SQL文章可看：用递归CTE按照树状结构展开物料分组 https://vip.kingdee.com/article/316540071887198464?productLineId=1