SQL Server 公用表表达式(CTE)实现递归的方法
程序员文章站
2022-04-07 14:37:05
公用表表达式简介:
公用表表达式 (cte) 可以认为是在单个 select、insert、update、delete 或 create view 语句的执行范围内定义的...
公用表表达式简介:
公用表表达式 (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_index on role_cte(id,parentid) with ( pad_index=on, fillfactor=50, drop_existing=off, statistics_norecompute=on ) select * from role_cte
查找指定节点的所有子孙节点:
使用普通 sql 语句实现:
declare @level int declare @node int declare @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=@node while(@@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(内连接)和自连接 end select 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: 是重点,不然会进入死循环,作用就是限制只插入一次。
如果需要限制循环的次数,即递归的层数,那么只需要在 while 条件里面添加一个限制即可。如下:
declare @level int declare @node int declare @num int declare @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=@node while(@@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(内连接)和自连接 end select 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_cte as ( 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 int set @node=3; set @num=1; with temp_cte as ( 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 int declare @node int declare @num int declare @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=@node end select 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 int set @node=8; set @num=2; with temp_cte as ( 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使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期
-
使用CTE公用表表达式的递归查询(WITH AS)
-
SQL Server 公用表表达式(CTE)实现递归的方法
-
mysql8 公用表表达式CTE的使用方法实例分析
-
sql server数据库性能优化之2-避免使用CTE公用表达式的递归【by zhang502219048】
-
SQL Server温故系列(3):SQL 子查询 & 公用表表达式 CTE
-
sql server 2005中使用with实现递归的方法
-
SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL
-
SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL
-
sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期