SQL Server 公用表表达式(CTE)实现递归的方法
作者:Brambling 发布时间:2024-01-26 15:20:10
标签:sqlserver,cte,递归
公用表表达式简介:
公用表表达式 (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:lv=@level-1 是重点,不然会进入死循环,作用就是限制只插入一次。
如果需要限制循环的次数,即递归的层数,那么只需要在 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)实现递归的方法网站的支持!
来源:http://www.cnblogs.com/Brambling/archive/2017/04/28/6778814.html
0
投稿
猜你喜欢
- callable函数可用于判断一个对象是否可以被调用,若对象可以被调用则返回True,反之则返回False。所谓可调用,是指代码里可以在对象
- 在业务稳定性要求比较高的情况下,运维为能及时发现问题,有时需要对应用程序的日志进行实时分析,当符合某个条件时就立刻报警,而不是被动等待出问题
- hmac模块hmac模块用于生成HMAC码。这个HMAC码可以用于验证消息的完整性,其原理也很简单,就是一种加入了密钥的消息摘要,相比起MA
- 这是我的xml文件结构<?xml version='1.0' encoding='utf-8'?>
- 本文实例为大家分享了Python将图片彩色转化为素描的具体代码,供大家参考,具体内容如下第一种:from PIL import Image,
- 做 Web 开发少不了要与模板引擎打交道。我陆续也接触了 Python 的不少模板引擎,感觉可以总结一下了。一、首先按照我的熟悉程度列一下:
- 注意转义字符\的使用\\,\",\$ 注意使用8进制或16进制字符表示 \xf6 echo "H\xf6me"
- 将try except中捕获到的异常信息输出到日志文件中,方便查找错误原因,tranceback模块提供了把详细出错堆栈信息格式化成字符串返
- 前言在 JavaWeb 开发中,一般使用 Zxing 来生成和识别二维码,但是,Zxing 的识别有点差强人意,不少相对模糊的二维码识别率很
- 1,使用到的第三方库requestsBeautifulSoup 美味汤worldcloud 词云jieba 中文分词matplotlib 绘
- 本文实例为大家分享了python3.6使用SMTP协议发送邮件的具体代码,供大家参考,具体内容如下代码如下:# !/usr/bin/pyth
- 随滚动条移动的DIV层js代码,无论你的滚动条到哪里这个DIV层就跟到哪里!代码中例举了五个方向的滚动div层例子:包括左上方的div,左下
- 该程序通过绘制树干(最初是树;后来是树枝)并递归地添加树来绘制“树”。 使用Pillow。利用递归函数绘制分形树(fractal tree)
- 有时候我们想要的数据合并结果是数据的轴向连接,在pandas中这可以通过concat来实现。操作的对象通常是Series。Ipython中的
- #!/usr/bin/env python#coding=utf-8import osfrom pyinotify import Watch
- 今年我们的祖国多灾多难 雪灾的阴影还没散去又发生了地震。让我们用手中的画笔为5.12地震受灾同胞们祈福并号召大家为灾区人民捐款!中国插画网祈
- 本文实例讲述了Python使用sorted排序的方法。分享给大家供大家参考,具体如下:# 例1. 按照元素出现的次数来排序seq = [2,
- 基本映射映射使用在根据不同URLs请求来产生相对应的返回内容.Bottle使用route() 修饰器来实现映射.from bottle im
- 一、python numpy + matplotlib 画股票k线图# -- coding: utf-8 --import requests
- 记得在面试腾讯实习生的时候,面试官问了我这样一道问题。//下述两种声明方式有什么不同 function foo(){};