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'))