几种常用数据库树形处理

提示一下:只对初学者,SQL的牛人多,你们就不要看了... 呵呵.

本文只贴出我自己在设计中常用到的,对于那些通用的,递归处理的,已经满天飞.所以在这里不描述了.

1.父/子 描述 + 层级索引 

我们一般描述一个有父子结构的组织时(如 BOM,地区,部门,商品分类 等),都习惯性地把它记录为 父/子 的形式,这样,在设计上有很高的弹性,可以无限分层.但在一些特殊的场合,如既要满足弹性的需求,又要满足速度的要求时,可以在原的基础上,加上一个层级的索引和层编号.并设置为唯一索引.

不过,这个有一个唯一的缺点,就是编码复杂.

下面的例子是以部门为例子,节点的层级索引和层编号,节点的移动,修改,等.都能过触发器来自动生成.

SQL code

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='BASE_Dept')    DROP Table BASE_DeptGO------------------------------------- 部门资料信息-----------------------------------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))  GOCREATE INDEX IX_BASE_Dept_VarSearch ON BASE_Dept(VarSearch,IntLayerID)   INCLUDE (BigDeptAuID,VarDeptName) GOCREATE INDEX IX_BASE_Dept_BigParDeptAuID ON BASE_Dept(BigParDeptAuID)  GOCREATE INDEX IX_BASE_Dept_VarDeptID ON BASE_Dept(VarDeptID) GOIF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='Vew_BASE_Dept')    DROP VIEW Vew_BASE_Dept    GOIF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='fun_BASE_Dept_Check_VarDeptID')    DROP FUNCTION fun_BASE_Dept_Check_VarDeptIDGO/*************************************************/--对 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 @VarValueENDGO--------------------------------------- 约束 VarDeptID 列.-------------------------------------ALTER TABLE BASE_DeptADD CHECK (ISNULL(VarDeptID,'')<>dbo.fun_BASE_Dept_Check_VarDeptID(BigDeptAuID,VarDeptID))/*************************************************/GOIF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='pro_BASE_Dept_CreateSearch')    DROP Proc pro_BASE_Dept_CreateSearchGO-------------------------------------------------------------------生成并插入搜索字符. -----------------------------------------------------------------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    ENDGO-------------------------------------------------------- 插入搜索字符.------------------------------------------------------CREATE TRIGGER TR_BASE_Dept_CreateSearch ON dbo.BASE_DeptAFTER 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     ENDENDGO-------------------------------------------------------部门节点移动.-----------------------------------------------------GOIF EXISTS(SELECT * FROM DBO.SYSOBJECTS WHERE [NAME] = 'pro_BASE_Dept_NodeMove')    DROP PROC pro_BASE_Dept_NodeMoveGOCreate 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----------结果--------BigParDeptAuIDBigDeptAuIDVarDeptIDVarSearchIntLayerIDVarDeptName01001,1,1总经理办公室12001001,1,2,2销售部13001002,1,3,2技术部34001002001,1,3,4,3数据库35001002002,1,3,5,3系统开发组36001002003,1,3,6,3美工组37001002004,1,3,7,3WEB组-------------------------------------------从结果看,VarSearch,IntLayerID 系统的确是生成了...
 
生成  VarSearch,IntLayerID 的目的就是:在取某个 节点 下的 所有节点 或某个 层 上的节点时,可以使用 LIKE 直接取到,免去了 递归. 为数据库提供了比较好的读性能.如,要取 总经理办公室 下所有的子.SELECT * FROM dbo.BASE_Dept WHERE VarSearch LIKE ',1,%'  SELECT * FROM dbo.BASE_Dept WHERE VarSearch>',1,'