二级物料，三级物料的Sql查找代码

 

Level 代表几级，V.Code目前写死，可能部署的时候要变。

A2.Name 就是对应料品分类名称

select  A2.Name as CatName, M.Segment1,A.Code, M.Code,M.* from CBO_ItemMaster M inner join Base_DefineValue A on M.Segment1=A.Code

left join Base_DefineValue_Trl A2 on A.ID=A2.ID

where  A.[Level]=3 and (A.[ValueSetDef] in (select V.ID from Base_ValueSetDef V  where V.Code='008'))

规格问题： SPEC 为规格  = 料品分类 + ’.’ + M.Segment2【料品编码字段2】

select  A2.Name as CatName,  A2.Name + '.' + M.Segment2 as SPEC,

 M.Segment1,A.Code, M.Code,M.* from CBO_ItemMaster M inner join Base_DefineValue A on M.Segment1=A.Code

left join Base_DefineValue_Trl A2 on A.ID=A2.ID

where A.[Level]=2 and (A.[ValueSetDef] in (select V.ID from Base_ValueSetDef V  where V.Code='008'))