侯体宗的博客
  • 首页
  • Hyperf版
  • beego仿版
  • 人生(杂谈)
  • 技术
  • 关于我
  • 更多分类
    • 文件下载
    • 文字修仙
    • 中国象棋ai
    • 群聊
    • 九宫格抽奖
    • 拼图
    • 消消乐
    • 相册

SQL Server 公用表表达式(CTE)实现递归的方法

数据库  /  管理员 发布于 5年前   204

公用表表达式简介:

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

下面先创建一个表,并插入一些数据:

create table Role_CTE( Id  int    not null, Name nvarchar(32) not null, ParentId int  not null )insert into Role_CTE(Id,Name,ParentId)select '1','超级管理员','0' union select '2','管理员A','1' union select '3','管理员B','2' union select '4','会员AA','2' union select '5','会员AB','2' union select '6','会员BA','3' union select '7','会员BB','3' union select '8','用户AAA','4' union select '9','用户BBA','7' -- 创建一个复合聚集索引create clustered index Clu_Role_CTE_Indexon Role_CTE(Id,ParentId)with( pad_index=on, fillfactor=50, drop_existing=off, statistics_norecompute=on)select * from Role_CTE

查找指定节点的所有子孙节点:

使用普通 sql 语句实现:

declare @level intdeclare @node intdeclare @ResTab table( node int not null, lv  int not null )set @level=0  -- 表示初始的等级set @node=3   --表示初始的节点ID,即从指定的哪个节点开始查找insert into @ResTab    -- 为表变量插入初始的数据select Id,@level from Role_CTE where Id=@nodewhile(@@ROWCOUNT>0)begin set @level=@level+1 insert into @ResTab select b.Id,@level  from @ResTab a  join Role_CTE b on a.node=b.ParentId and lv=@level-1 -- join 等于 inner join(内连接)和自连接endselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id

以上是根据指定节点ID(3),查找父节点ID(即字段 ParentId)等于指定的节点ID,如果有就插入,并继续循环。

PS:lv=@level-1 是重点,不然会进入死循环,作用就是限制只插入一次。

如果需要限制循环的次数,即递归的层数,那么只需要在 while 条件里面添加一个限制即可。如下:

declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv  int not null )set @level=0  -- 表示初始的等级set @node=3   --表示初始的节点ID,即从指定的哪个节点开始查找set @num=1  -- 指定递归层级,即循环的次数insert into @ResTab    -- 为表变量插入初始的数据select Id,@level from Role_CTE where Id=@nodewhile(@@ROWCOUNT>0 and @level<@num)begin set @level=@level+1 insert into @ResTab select b.Id,@level  from @ResTab a  join Role_CTE b on a.node=b.ParentId and lv=@level-1 -- join 等于 inner join(内连接)和自连接endselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id

当然,如果指定了循环次数,就可以不用 while 判断语句的 @@rowcount>0 了。

使用 SQL CTE 实现:

declare @node int set @node=3;with temp_cteas( select Id,Name,0 lv  -- 查询出“根节点”,即指定的起始节点 from Role_CTE  where Id=@node  union all select b.Id,b.Name,a.lv+1  from temp_cte a  join Role_CTE b on a.Id=b.ParentId)select * from temp_cte

使用 CTE 控制递归的层数,与上面类似。如下:

declare @node int declare @num intset @node=3;set @num=1;with temp_cteas( select Id,Name,0 lv  -- 查询出“根节点”,即指定的起始节点 from Role_CTE  where Id=@node  union all select b.Id,b.Name,a.lv+1  from temp_cte a  join Role_CTE b on a.Id=b.ParentId     and a.lv<@num  --控制递归层数)select * from temp_cte

查找指定节点的所有祖先节点:

使用普通 sql 语句实现:

declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv  int not null )set @level=0 -- 表示初始的等级set @node=8   --表示初始的节点ID,即从指定的哪个节点开始查找set @num=2  -- 指定递归层级,即循环的次数while(@level<=@num and @node is not null) -- 如果为空就表示没有查到父级了begin insert into @ResTab select @node,@level set @level=@level+1 select @node=ParentId  from Role_CTE  where Id=@nodeendselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id

使用 SQL CTE 实现:

declare @node int declare @num intset @node=8;set @num=2;with temp_cteas( select Id,Name,ParentId,0 lv  -- 查询出“根节点”,即指定的起始节点 from Role_CTE  where Id=@node  union all select b.Id,b.Name,b.ParentId,a.lv+1  from temp_cte a  join Role_CTE b on a.ParentId=b.Id     and a.lv < @num  --控制递归层数)select * from temp_cte

以上所述是小编给大家介绍的SQL Server 公用表表达式(CTE)实现递归的方法,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的,在此也非常感谢大家对站的支持!


  • 上一条:
    SQL Server 常用函数使用方法小结
    下一条:
    必备 SQL 查询优化技巧提升网站访问速度
  • 昵称:

    邮箱:

    0条评论 (评论内容有缓存机制,请悉知!)
    最新最热
    • 分类目录
    • 人生(杂谈)
    • 技术
    • linux
    • Java
    • php
    • 框架(架构)
    • 前端
    • ThinkPHP
    • 数据库
    • 微信(小程序)
    • Laravel
    • Redis
    • Docker
    • Go
    • swoole
    • Windows
    • Python
    • 苹果(mac/ios)
    • 相关文章
    • 分库分表的目的、优缺点及具体实现方式介绍(0个评论)
    • DevDB - 在 VS 代码中直接访问数据库(0个评论)
    • 在ubuntu系统中实现mysql数据存储目录迁移流程步骤(0个评论)
    • 在mysql中使用存储过程批量新增测试数据流程步骤(0个评论)
    • php+mysql数据库批量根据条件快速更新、连表更新sql实现(0个评论)
    • 近期文章
    • 在go+gin中使用"github.com/skip2/go-qrcode"实现url转二维码功能(0个评论)
    • 在go语言中使用api.geonames.org接口实现根据国际邮政编码获取地址信息功能(1个评论)
    • 在go语言中使用github.com/signintech/gopdf实现生成pdf分页文件功能(0个评论)
    • gmail发邮件报错:534 5.7.9 Application-specific password required...解决方案(0个评论)
    • 欧盟关于强迫劳动的规定的官方举报渠道及官方举报网站(0个评论)
    • 在go语言中使用github.com/signintech/gopdf实现生成pdf文件功能(0个评论)
    • Laravel从Accel获得5700万美元A轮融资(0个评论)
    • 在go + gin中gorm实现指定搜索/区间搜索分页列表功能接口实例(0个评论)
    • 在go语言中实现IP/CIDR的ip和netmask互转及IP段形式互转及ip是否存在IP/CIDR(0个评论)
    • PHP 8.4 Alpha 1现已发布!(0个评论)
    • 近期评论
    • 122 在

      学历:一种延缓就业设计,生活需求下的权衡之选中评论 工作几年后,报名考研了,到现在还没认真学习备考,迷茫中。作为一名北漂互联网打工人..
    • 123 在

      Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..
    • 原梓番博客 在

      在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..
    • 博主 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..
    • 1111 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..
    • 2017-06
    • 2017-08
    • 2017-09
    • 2017-10
    • 2017-11
    • 2018-01
    • 2018-05
    • 2018-10
    • 2018-11
    • 2020-02
    • 2020-03
    • 2020-04
    • 2020-05
    • 2020-06
    • 2020-07
    • 2020-08
    • 2020-09
    • 2021-02
    • 2021-04
    • 2021-07
    • 2021-08
    • 2021-11
    • 2021-12
    • 2022-02
    • 2022-03
    • 2022-05
    • 2022-06
    • 2022-07
    • 2022-08
    • 2022-09
    • 2022-10
    • 2022-11
    • 2022-12
    • 2023-01
    • 2023-03
    • 2023-04
    • 2023-05
    • 2023-07
    • 2023-08
    • 2023-10
    • 2023-11
    • 2023-12
    • 2024-01
    • 2024-03
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客