• MSSQLServer中组织或分类表的设计及其递归查询


    开篇:项目中用到上下级从属关系的太多太多了,如:组织、分类、行政区域,这里不再一一介绍,遇到这种的如何去进行数据库表的设计及其应用的,个人对往期项目中所涉及到的进行了一些总结。

    数据库表设计:表字段一般含有: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
    View Code

    创建触发器

    --自动生成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;
    View Code

    插入测试数据

    --创建触发器成功后插入其他非根节点数据
    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);
    View Code

    递归查询某一个节点及其所有子节点,常规查询方法 就是递归查询

    --递归查询   查询某一节点下所有子节点
    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
    View Code

    另一种算法就是按照Codepath来查询了,这里就知道CodePath,NamePath的重要性了,这个就比较简单了

    select * from Tree where CodePath like '000000000%'—看来加了路径能方便检索

    实际应用:实际应用中需要注意的几点

    1.如果是行政区域,组织这些不轻易改动的,可以使用缓存或者根据数据库表生成json格式或其他常用格式的物理文件,能大大提高速度

    2.对于改动比较大,且改动经常是层级改动:如原来的子级经常变化为平级或者父级,且改动量极大的,需要考虑触发器性能及修改降级或者升级后原来的子级如何变化,本触发器不涉及到原来的子级

    最后总结:设计模式没有最好至少更合适,如果大家有其他思路,欢迎交流,可以直接回复评论或联系我,feifei12300@126.com,QQ:251608027

  • 相关阅读:
    一致Hash算法
    Ubuntu14.04上深度学习Caffe库安装指南(CUDA7.5 + opencv3.1)
    springcloud 服务调用的两种方式
    Spring Boot学习记录(二)–thymeleaf模板
    Springmvc构造RESTful详细讲解
    pring MVC过滤器-HttpPutFormContentFilter
    Spring MVC中各个filter的用法
    Spring MVC 使用拦截器 HiddenHttpMethodFilter配置Rest风格的URL
    springmvc 入门(1)
    oracle sql语言模糊查询--通配符like的使用教程
  • 原文地址:https://www.cnblogs.com/fly12300/p/6150030.html
Copyright © 2020-2023  润新知