开篇:项目中用到上下级从属关系的太多太多了,如:组织、分类、行政区域,这里不再一一介绍,遇到这种的如何去进行数据库表的设计及其应用的,个人对往期项目中所涉及到的进行了一些总结。
数据库表设计:表字段一般含有:ID,Code,Name,ParentCode,ParentName,CodePath,NamePath,Level,IsNotLast,这里解释一下CodePath,NamePath,主要是为了后续方便查询使用,Level是为了方便层级检索,IsNotLast是是否最后一级,这个作用在行政区域、组织层级中作用比较大,分类中作用不大。
既然有CodePath,NamePath那么问题来了,如何自动生成这两个值呢,可以建触发器也可以把这两列做成计算列,这里以触发器为例:
触发器前期条件:1.必须有根节点,这个根节点是逻辑根节点,在进行逻辑判断及查询时方便操作,可能在实际中没有作用;
2.插入数据时必须保证父节点存在,如果没有父节点就以逻辑根节点为父节点。
在满足以上量个前提下,可以继续下一步操作:
光说不练假把式,直接上代码:
建表语句:
------创建表Tree if not exists(select * from sysobjects where name='Tree') begin create table Tree(ID int identity(1,1) primary key,Code varchar(32),Name nvarchar(128), ParentCode varchar(32) ,ParentName nvarchar(32),CodePath nvarchar(1024),NamePath nvarchar(1024),Level int,IsNotLast int); insert into Tree values ('000000000','中国',null,null,null,null,0,0);--创建根节点 中国是根节点 end else begin drop table Tree create table Tree(ID int identity(1,1) primary key,Code varchar(32),Name nvarchar(128), ParentCode varchar(32) ,ParentName nvarchar(32),CodePath nvarchar(1024),NamePath nvarchar(1024),Level int,IsNotLast int); insert into Tree values ('000000000','中国',null,null,null,null,0,0);--创建根节点 中国是根节点 end go
创建触发器
--自动生成CodePath,NamePath --建立触发器,或者自动计算列均可,这里创建触发器,每次插入或者更新时根据子节点找父节点,层层向上找到并自动更新CodePath,NamePath两列 --此触发器有一个缺陷,比如你仅仅更新了父节点,但是该父节点下的子节点的NamePath,CodePath不会自动更新 --这里不再讨论全局更新问题,后续在进行介绍,可以根据根节点,层层向下找到子节点进行更新,或者定时自动更新 create trigger Trigger_Update_CodePathAndNamePathByCode_After_Insert_For_Tree on Tree after insert,update as set nocount on DECLARE @ID int, @Code varchar(32), @ParentCode varchar(32), @Name nvarchar(500), @CodePath varchar(1024), @NamePath nvarchar(1024); --查询更新或插入的ID,CodePath(默认是Code),NamePath(默认是Name),ParentCode DECLARE cur_Inserted CURSOR FOR SELECT ID FROM Inserted;--从Inserted表创建游标 OPEN cur_Inserted; FETCH NEXT FROM cur_Inserted INTO @ID--,@Code,@ParentCode,@Name,@CodePath,@NamePath; WHILE(@@Fetch_Status=0) begin select @ID = ID,@CodePath = Code,@NamePath = Name,@ParentCode = ParentCode from Tree where ID = @ID; while (@ParentCode is not null) begin select @Code=Code,@Name=Name,@ParentCode=ParentCode from Tree where Code = @ParentCode; set @CodePath = @Code + '' + @CodePath; set @NamePath = @Name + '' + @NamePath; if(@ParentCode = @Code) set @ParentCode = null; end IF(@CodePath is null and @NamePath is null) UPDATE [Tree] SET [CodePath]=NULL, [NamePath]=NULL WHERE [ID] = @ID;--单条记录 ELSE UPDATE [Tree] SET [CodePath]=@CodePath, [NamePath]=@NamePath WHERE [ID] = @ID;--单条记录 FETCH NEXT FROM cur_Inserted INTO @ID --,@Code,@ParentCode,@Name,@CodePath,@NamePath; end close cur_Inserted Deallocate cur_Inserted; set nocount off;
插入测试数据
--创建触发器成功后插入其他非根节点数据 insert into Tree values ('000000011','北京市','000000000','中国',null,null,1,0); insert into Tree values ('000001101','市辖区','000000011','北京市',null,null,2,0); insert into Tree values ('000110101','东城区','000001101','北京市',null,null,3,0); insert into Tree values ('110101001','东华门街道','000110101','东城区',null,null,4,1); insert into Tree values ('000000013','河北省','000000000','中国',null,null,1,0); insert into Tree values ('000001301','石家庄市','000000013','河北省',null,null,2,0); insert into Tree values ('000130101','市辖区','000001301','石家庄市',null,null,3,0); insert into Tree values ('000130102','长安区','000001301','石家庄市',null,null,3,0); insert into Tree values ('130102001','建北街道','000130102','长安区',null,null,4,1); insert into Tree values ('000130125','行唐县','000001301','石家庄市',null,null,3,0); insert into Tree values ('130125205','城寨乡','000130125','行唐县',null,null,4,1);
递归查询某一个节点及其所有子节点,常规查询方法 就是递归查询
--递归查询 查询某一节点下所有子节点 with Trees(ID,Code,Name,ParentCode,CodePath,NamePath,Level,ZLevel) as(select t.ID,t.Code,t.Name,t.ParentCode,t.CodePath,t.NamePath,t.Level,t.Level as ZLevel from Tree t where t.ParentCode = '000000000' --递归的首先找到根节点也就是根节点 union all select t.ID,t.Code,t.Name,t.ParentCode,t.CodePath,t.NamePath,t.Level,ZLevel + 1 from Tree t--原表 join Trees t2 on t2.Code = t.ParentCode) --递归表与原表join,关系为通过递归表中的Code找到原表中的ParentCode成员调用 select * from Trees order by Level,ZLevel go
另一种算法就是按照Codepath来查询了,这里就知道CodePath,NamePath的重要性了,这个就比较简单了
select * from Tree where CodePath like '000000000%'—看来加了路径能方便检索
实际应用:实际应用中需要注意的几点
1.如果是行政区域,组织这些不轻易改动的,可以使用缓存或者根据数据库表生成json格式或其他常用格式的物理文件,能大大提高速度
2.对于改动比较大,且改动经常是层级改动:如原来的子级经常变化为平级或者父级,且改动量极大的,需要考虑触发器性能及修改降级或者升级后原来的子级如何变化,本触发器不涉及到原来的子级
最后总结:设计模式没有最好至少更合适,如果大家有其他思路,欢迎交流,可以直接回复评论或联系我,feifei12300@126.com,QQ:251608027