金蝶云星空——用递归SQL查询物料分组

应用场景：

金蝶物料分组为树形结构，需要根据SQL查询同步到第三方系统中。

---

技术实现

用递归CTE按照树状结构展开物料分组

with cte
as(      
        select 0 as 物料分组层级
               ,t1.FID
               ,case when isnull(t1.FFULLPARENTID,'') = '' then '.'+CAST(t1.FID AS  VARCHAR(255)) else t1.FFULLPARENTID end as FFULLPARENTID                   
               ,t1.FNUMBER
               ,cast(t1.FNUMBER as varchar(max)) as FullNumber
               ,t2.FNAME
               ,cast(t2.FNAME as varchar(max)) as FullNAME
        from T_BD_MATERIALGROUP t1            
               join T_BD_MATERIALGROUP_L t2                 
                       on t1.fid = t2.FID and t2.FLOCALEID = 2052    
        where FPARENTID =0      
        union all            
         
        select a1.物料分组层级+1 as 当前层级
               ,t1.FID
               ,case when isnull(t1.FFULLPARENTID,'') = '' then '.'+CAST(t1.FID AS  VARCHAR(255)) else t1.FFULLPARENTID end as FFULLPARENTID                   
               ,t1.FNUMBER
               ,cast(a1.FullNumber+'_'+t1.FNUMBER as varchar(max)) as FullNumber
               ,t2.FNAME
               ,cast(a1.FullNAME+'_'+t2.FNAME as varchar(max)) as FullNAME
         from cte a1           
               join T_BD_MATERIALGROUP t1    
                       on t1.FPARENTID = a1.FID
               join T_BD_MATERIALGROUP_L t2
                       on t1.fid = t2.FID and t2.FLOCALEID = 2052
)
select 物料分组层级,FNUMBER AS 分组编码,FullNumber as 分组全编码,FullNAME as 分组全名,FNAME as 分组名称--,FID,FFULLPARENTID
from cte --where 物料分组层级 = 0
order by FullNumber,FullNAME,物料分组层级;