[sqlServer]层状设计

最新推荐文章于 2024-09-15 17:41:58 发布

原创最新推荐文章于 2024-09-15 17:41:58 发布 · 860 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #join

本文介绍了一种通过SQL查询构建物料分类层级视图的方法，实现了从一级到六级物料类别的叠加显示，并通过不同长度的编码区分层级，便于管理和查询。

原表结构：

Summary
存货分类档案
Columns

Column Name	Description	Datatype	Length	Allow Nulls
cInvCCode	存货大类编码	varchar	12	False
cInvCName	存货大类名称	varchar	20	False
iInvCGrade	编码级次	tinyint	1	False
bInvCEnd	是否末级	bit	1	True
cEcoCode	所属经济分类编码	varchar	2	True
cBarCode	对应条形码编码	varchar	30	True
pubufts	时间戳	timestamp	8	True

意图：一级物料类别名称－－－二级物料类别名称－－－三级物料类别名称

将此物料的类别进行层状叠加
建立试图

ALTER view v_leniz_step_inventoryclass as
select
vl.cinvccode,
vl.classCode1, isNUll(invl1.cinvcname,'') as className1,
vl.classCode2, isNUll(invl2.cinvcname,'') as className2,
vl.classCode3, isNUll(invl3.cinvcname,'') as className3,
vl.classCode4, isNUll(invl4.cinvcname,'') as className4,
vl.classCode5, isNUll(invl5.cinvcname,'') as className5,
vl.classCode6, isNUll(invl6.cinvcname,'') as className6
from
v_leniz_inventoryclass vl
left join inventoryclass invl1 on vl.classCode1 = invl1.cinvccode
left join inventoryclass invl2 on vl.classCode2 = invl2.cinvccode
left join inventoryclass invl3 on vl.classCode3 = invl3.cinvccode
left join inventoryclass invl4 on vl.classCode4 = invl4.cinvccode
left join inventoryclass invl5 on vl.classCode5 = invl5.cinvccode
left join inventoryclass invl6 on vl.classCode6 = invl6.cinvccode

试图二：

create view v_leniz_stepclass as
SELECT
   cinvccode as classCode,
   CASE
      WHEN len(cinvccode)=1 Then className1
      WHEN len(cinvccode)=3 Then className1 + '-' + className2
      WHEN len(cinvccode)=5 Then className1 + '-' + className2 + '-' + className3
      WHEN len(cinvccode)=7 Then className1 + '-' + className2 + '-' + className3 + '-' + className4
      WHEN len(cinvccode)=9 Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5
      WHEN len(cinvccode)=11 Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5 + '-' + className6
      ELSE ''
   END AS className
FROM v_leniz_step_inventoryclass