提示一下:只对初学者,SQL的牛人多,你们就不要看了... 呵呵.
本文只贴出我自己在设计中常用到的,对于那些通用的,递归处理的,已经满天飞.所以在这里不描述了.
1.父/子 描述 + 层级索引
我们一般描述一个有父子结构的组织时(如 BOM,地区,部门,商品分类 等),都习惯性地把它记录为 父/子 的形式,这样,在设计上有很高的弹性,可以无限分层.但在一些特殊的场合,如既要满足弹性的需求,又要满足速度的要求时,可以在原的基础上,加上一个层级的索引和层编号.并设置为唯一索引.
不过,这个有一个唯一的缺点,就是编码复杂.
下面的例子是以部门为例子,节点的层级索引和层编号,节点的移动,修改,等.都能过触发器来自动生成.
本文只贴出我自己在设计中常用到的,对于那些通用的,递归处理的,已经满天飞.所以在这里不描述了.
1.父/子 描述 + 层级索引
我们一般描述一个有父子结构的组织时(如 BOM,地区,部门,商品分类 等),都习惯性地把它记录为 父/子 的形式,这样,在设计上有很高的弹性,可以无限分层.但在一些特殊的场合,如既要满足弹性的需求,又要满足速度的要求时,可以在原的基础上,加上一个层级的索引和层编号.并设置为唯一索引.
不过,这个有一个唯一的缺点,就是编码复杂.
下面的例子是以部门为例子,节点的层级索引和层编号,节点的移动,修改,等.都能过触发器来自动生成.
-
SQL code
-
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
弄点测试数据.注意到 VarSearch , IntLayerID 是''和0,真正的 VarSearch 和 IntLayerID,将由触发器去生成. ------------------------------------------ ---插入测试的字符串. ------------------------------------------ INSERT INTO [BASE_Dept] ([BigParDeptAuID] ,[BigDeptAuID] ,[VarDeptID] ,[VarSearch] ,[IntLayerID] ,[VarDeptName]) VALUES (0 ,1 ,'001' ,'' ,0 ,'总经理办公室') INSERT INTO [BASE_Dept] ([BigParDeptAuID] ,[BigDeptAuID] ,[VarDeptID] ,[VarSearch] ,[IntLayerID] ,[VarDeptName]) VALUES (1 ,2 ,'001001' ,'' ,0 ,'销售部') INSERT INTO [BASE_Dept] ([BigParDeptAuID] ,[BigDeptAuID] ,[VarDeptID] ,[VarSearch] ,[IntLayerID] ,[VarDeptName]) VALUES (1 ,3 ,'001002' ,'' ,0 ,'技术部') INSERT INTO [BASE_Dept] ([BigParDeptAuID] ,[BigDeptAuID] ,[VarDeptID] ,[VarSearch] ,[IntLayerID] ,[VarDeptName]) VALUES (3 ,4 ,'001002001' ,'' ,0 ,'数据库') INSERT INTO [BASE_Dept] ([BigParDeptAuID] ,[BigDeptAuID] ,[VarDeptID] ,[VarSearch] ,[IntLayerID] ,[VarDeptName]) VALUES (3 ,5 ,'001002002' ,'' ,0 ,'系统开发组') INSERT INTO [BASE_Dept] ([BigParDeptAuID] ,[BigDeptAuID] ,[VarDeptID] ,[VarSearch] ,[IntLayerID] ,[VarDeptName]) VALUES (3 ,6 ,'001002003' ,'' ,0 ,'美工组') INSERT INTO [BASE_Dept] ([BigParDeptAuID] ,[BigDeptAuID] ,[VarDeptID] ,[VarSearch] ,[IntLayerID] ,[VarDeptName]) VALUES (3 ,7 ,'001002004' ,'' ,0 ,'WEB组') GO看一下结果: SELECT * FROM dbo.BASE_Dept -------- --结果 -------- BigParDeptAuID BigDeptAuID VarDeptID VarSearch IntLayerID VarDeptName 0 1 001 ,1, 1 总经理办公室 1 2 001001 ,1,2, 2 销售部 1 3 001002 ,1,3, 2 技术部 3 4 001002001 ,1,3,4, 3 数据库 3 5 001002002 ,1,3,5, 3 系统开发组 3 6 001002003 ,1,3,6, 3 美工组 3 7 001002004 ,1,3,7, 3 WEB组 ------------------------------------------- 从结果看,VarSearch,IntLayerID 系统的确是生成了...生成 VarSearch,IntLayerID 的目的就是:在取某个 节点 下的 所有节点 或某个 层 上的节点时,可以使用 LIKE 直接取到,免去了 递归. 为数据库提供了比较好的读性能. 如,要取 总经理办公室 下所有的子. SELECT * FROM dbo.BASE_Dept WHERE VarSearch LIKE ',1,%' SELECT * FROM dbo.BASE_Dept WHERE VarSearch>',1,'
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
