MS-SQL Server
[原] 几种常用数据库树形处理
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区提示一下:只对初学者,SQL的牛人多,你们就不要看了... 呵呵.
本文只贴出我自己在设计中常用到的,对于那些通用的,递归处理的,已经满天飞.所以在这里不描述了.
1.父/子 描述 + 层级索引
我们一般描述一个有父子结构的组织时(如 BOM,地区,部门,商品分类 等),都习惯性地把它记录为 父/子 的形式,这样,在设计上有很高的弹性,可以无限分层.但在一些特殊的场合,如既要满足弹性的需求,又要满足速度的要求时,可以在原的基础上,加上一个层级的索引和层编号.并设置为唯一索引.
不过,这个有一个唯一的缺点,就是编码复杂.
下面的例子是以部门为例子,节点的层级索引和层编号,节点的移动,修改,等.都能过触发器来自动生成.
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='BASE_Dept')
DROP Table BASE_Dept
GO
-----------------------------------
-- 部门资料信息
-----------------------------------
CREATE TABLE BASE_Dept(
BigParDeptAuID BigInt Not NULL,--父系统编号
BigDeptAuID BigInt Not NULL,--系统编号
VarDeptID VarChar(32) ,--部门编号[人工录入]
VarSearch VarChar(256) Not NULL,--检索编码用于 LINK 检索，系统生成。格式如： ,12,23,54,
IntLayerID Int Not NULL,--检索层编号. IntLayerID.[ 顶层为 1 ]
VarDeptName VarChar(64) Not NULL,--部门中文名称 名称=所属组织机构名称+部门名称+组别名称
CONSTRAINT PK_BASE_Dept PRIMARY KEY CLUSTERED
(
BigDeptAuID ASC
))
GO
CREATE INDEX IX_BASE_Dept_VarSearch ON BASE_Dept(VarSearch,IntLayerID)
INCLUDE (BigDeptAuID,VarDeptName)
GO
CREATE INDEX IX_BASE_Dept_BigParDeptAuID ON BASE_Dept(BigParDeptAuID)
GO
CREATE INDEX IX_BASE_Dept_VarDeptID ON BASE_Dept(VarDeptID)
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='Vew_BASE_Dept')
DROP VIEW Vew_BASE_Dept
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='fun_BASE_Dept_Check_VarDeptID')
DROP FUNCTION fun_BASE_Dept_Check_VarDeptID
GO
/*************************************************/
--对 VarDeptID 列进行约束检查函数.
--约束规则:表中不能出现两个相同的部门编号.
/*************************************************/
CREATE FUNCTION fun_BASE_Dept_Check_VarDeptID(
@BigDeptAuID BigInt,
@VarDeptID VarChar(32))
RETURNS VarChar(32) BEGIN
IF ISNULL(@VarDeptID,'')='' RETURN 'E5E8ED3D3B2C4645AE51DD6EA8FA0FBE'
DECLARE @VarValue VarChar(32)
select @VarValue=VarDeptID from BASE_Dept where VarDeptID=@VarDeptID And BigDeptAuID<>@BigDeptAuID
IF ISNULL(@VarValue,'')='' SET @VarValue='E5E8ED3D3B2C4645AE51DD6EA8FA0FBE'
RETURN @VarValue
END
GO
-------------------------------------
-- 约束 VarDeptID 列.
-------------------------------------
ALTER TABLE BASE_Dept
ADD CHECK (ISNULL(VarDeptID,'')<>dbo.fun_BASE_Dept_Check_VarDeptID(BigDeptAuID,VarDeptID))
/*************************************************/
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='pro_BASE_Dept_CreateSearch')
DROP Proc pro_BASE_Dept_CreateSearch
GO
-----------------------------------------------------------------
--生成并插入搜索字符.
-----------------------------------------------------------------
CREATE PROC pro_BASE_Dept_CreateSearch(
@BigDeptAuID BIGINT
) AS BEGIN
DECLARE @VarSearch VarChar(256)
DECLARE @IntLayerID Int
DECLARE @IntLoop Int
DECLARE @BigParDeptAuID BigInt
DECLARE @BigNextAuID BigInt
SET @IntLoop=0
SET @BigNextAuID=@BigDeptAuID
SET @VarSearch=',' + LTRIM(@BigNextAuID) + ','
SET @IntLayerID=1
IF NOT EXISTS(SELECT 1 FROM BASE_Dept WHERE BigDeptAuID=@BigDeptAuID) BEGIN
RETURN -1
END
WHILE @IntLoop<999 BEGIN
SET @BigParDeptAuID=0
SELECT @BigParDeptAuID=BigParDeptAuID FROM BASE_Dept WHERE BigDeptAuID=@BigNextAuID
IF NOT EXISTS(SELECT 1 FROM BASE_Dept WHERE BigDeptAuID=@BigParDeptAuID) BEGIN
BREAK;
END ELSE BEGIN
SET @BigNextAuID=@BigParDeptAuID
SET @IntLoop=@IntLoop+1
SET @IntLayerID=@IntLayerID+1
SET @VarSearch=',' + LTRIM(@BigNextAuID) + @VarSearch
END
END
ENDLOOP:
IF @IntLayerID=1 BEGIN
UPDATE BASE_Dept SET VarSearch=@VarSearch,IntLayerID=@IntLayerID,BigParDeptAuID=0 WHERE BigDeptAuID=@BigDeptAuID
END ELSE BEGIN
UPDATE BASE_Dept SET VarSearch=@VarSearch,IntLayerID=@IntLayerID WHERE BigDeptAuID=@BigDeptAuID
END
RETURN 0
END
GO
------------------------------------------------------
-- 插入搜索字符.
------------------------------------------------------
CREATE TRIGGER TR_BASE_Dept_CreateSearch ON dbo.BASE_Dept
AFTER INSERT, UPDATE AS BEGIN
DECLARE @BigAuID BIGINT
SELECT @BigAuID=MIN(BigDeptAuID) FROM INSERTED
WHILE NOT @BigAuID IS NULL BEGIN
EXEC pro_BASE_Dept_CreateSearch @BigAuID
SELECT @BigAuID=MIN(BigDeptAuID) FROM INSERTED WHERE BigDeptAuID>@BigAuID
END
END
GO
-----------------------------------------------------
--部门节点移动.
-----------------------------------------------------
GO
IF EXISTS(SELECT * FROM DBO.SYSOBJECTS WHERE [NAME] = 'pro_BASE_Dept_NodeMove')
DROP PROC pro_BASE_Dept_NodeMove
GO
Create Proc pro_BASE_Dept_NodeMove(
@BigDeptAuid Bigint,
@BigNewParDeptAuID Bigint
) AS BEGIN
IF @BigDeptAuid=@BigNewParDeptAuID RETURN -1
IF @BigDeptAuid<=0 RETURN -1
DECLARE @BigOleParDeptAuID BIGINT
DECLARE @IntOleParLayerID Int
DECLARE @IntNewParLayerID Int
DECLARE @IntDltLayer Int
DECLARE @VarOleSearch VarChar(256)
DECLARE @VarOPSearch VarChar(256)
DECLARE @VarNPSearch VarChar(256)
SELECT @BigOleParDeptAuID=BigParDeptAuID,@VarOleSearch=VarSearch FROM BASE_Dept WHERE BigDeptAuid=@BigDeptAuid
SET @BigOleParDeptAuID=ISNULL(@BigOleParDeptAuID,0)
SET @VarOleSearch=ISNULL(@VarOleSearch,'')
SELECT @IntOleParLayerID=IntLayerID,@VarOPSearch=VarSearch FROM BASE_Dept WHERE BigDeptAuid=@BigOleParDeptAuID
SET @IntOleParLayerID=ISNULL(@IntOleParLayerID,0)
SET @VarOPSearch=ISNULL(@VarOPSearch,'')
SELECT @IntNewParLayerID=IntLayerID,@VarNPSearch=VarSearch FROM BASE_Dept WHERE BigDeptAuid=@BigNewParDeptAuID
SET @IntDltLayer=ISNULL(@IntNewParLayerID,0)-ISNULL(@IntOleParLayerID,0)
ALTER TABLE BASE_Dept DISABLE TRIGGER TR_BASE_Dept_CreateSearch
UPDATE BASE_Dept SET BigParDeptAuID=@BigNewParDeptAuID WHERE BigDeptAuid=@BigDeptAuid
IF @BigOleParDeptAuID=0 BEGIN
IF @BigNewParDeptAuID<>0 BEGIN
UPDATE BASE_Dept
SET VarSearch= @VarNPSearch + SUBSTRING(VarSearch,2,LEN(VarSearch)-1),
IntLayerID=IntLayerID + @IntDltLayer
WHERE VarSearch LIKE @VarOleSearch + '%'
END
END ELSE IF @BigOleParDeptAuID>0 BEGIN
IF @BigNewParDeptAuID=0 BEGIN
UPDATE BASE_Dept
SET VarSearch=REPLACE(VarSearch,@VarOPSearch,','),
IntLayerID=IntLayerID + @IntDltLayer
WHERE VarSearch LIKE @VarOleSearch + '%'
END ELSE IF @BigNewParDeptAuID<>0 BEGIN
UPDATE BASE_Dept
SET VarSearch=REPLACE(VarSearch,@VarOPSearch,@VarNPSearch),
IntLayerID=IntLayerID + @IntDltLayer
WHERE VarSearch LIKE @VarOleSearch + '%'
END
END
ALTER TABLE BASE_Dept ENABLE TRIGGER TR_BASE_Dept_CreateSearch
END
...全文
相关推荐
excel树形字典，导入数据库
树形的材料字典，从excel导入到数据库中
深入理解小型数据库db-4.6.21的编程实践
Berkeley DB是一款开源的嵌入式数据库管理系统，提供灵活的数据存储解决方案。db-4.6.21作为其版本中的一个代表，包含了数据库核心功能，以及一系列对数据操作的优化。字典数据库，通常被称为键值存储数据库，是数据存储模型中最简单的一种，它与传统的基于表的关系数据库有所不同。字典数据库中的数据以键值对的形式存储。在这里，每个键都是唯一的，用来标识数据项，而值则是实际存储的数据。
JSON复杂数据处理之Json树形结构数据转Java对象并存储到数据库的实现
崇德易城市数据
动态树形菜单数据库表设计
这是一篇pdf格式的论文。很详细地图文并茂地讲解了树形菜单的设计。并分析了常规设计方式的不足。以冗余数据的方式设计了高效的树形菜单表。
常用的三种树形菜单->很实用的
NULL 博文链接：https://robinsoncrusoe.iteye.com/blog/825582
