这篇文章给大家介绍SQL Server中怎么利用公用表表达式实现递归，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

公用表表达式简介：

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

下面先创建一个表，并插入一些数据：

createtableRole_CTE(Idintnotnull,Namenvarchar(32)notnull,ParentIdintnotnull)insertintoRole_CTE(Id,Name,ParentId)select'1','超级管理员','0'unionselect'2','管理员A','1'unionselect'3','管理员B','2'unionselect'4','会员AA','2'unionselect'5','会员AB','2'unionselect'6','会员BA','3'unionselect'7','会员BB','3'unionselect'8','用户AAA','4'unionselect'9','用户BBA','7'--创建一个复合聚集索引createclusteredindexClu_Role_CTE_IndexonRole_CTE(Id,ParentId)with(pad_index=on,fillfactor=50,drop_existing=off,statistics_norecompute=on)select*fromRole_CTE

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

使用普通 sql 语句实现：

declare@levelintdeclare@nodeintdeclare@ResTabtable(nodeintnotnull,lvintnotnull)set@level=0--表示初始的等级set@node=3--表示初始的节点ID,即从指定的哪个节点开始查找insertinto@ResTab--为表变量插入初始的数据selectId,@levelfromRole_CTEwhereId=@nodewhile(@@ROWCOUNT>0)beginset@level=@level+1insertinto@ResTabselectb.Id,@levelfrom@ResTabajoinRole_CTEbona.node=b.ParentIdandlv=@level-1--join等于innerjoin（内连接）和自连接endselecta.node,b.Name,a.lvfrom@ResTabaleftjoinRole_CTEbona.node=b.Id

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

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

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

declare@levelintdeclare@nodeintdeclare@numintdeclare@ResTabtable(nodeintnotnull,lvintnotnull)set@level=0--表示初始的等级set@node=3--表示初始的节点ID,即从指定的哪个节点开始查找set@num=1--指定递归层级，即循环的次数insertinto@ResTab--为表变量插入初始的数据selectId,@levelfromRole_CTEwhereId=@nodewhile(@@ROWCOUNT>0and@level<@num)beginset@level=@level+1insertinto@ResTabselectb.Id,@levelfrom@ResTabajoinRole_CTEbona.node=b.ParentIdandlv=@level-1--join等于innerjoin（内连接）和自连接endselecta.node,b.Name,a.lvfrom@ResTabaleftjoinRole_CTEbona.node=b.Id

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

使用 SQL CTE 实现：

declare@nodeintset@node=3;withtemp_cteas(selectId,Name,0lv--查询出“根节点”，即指定的起始节点fromRole_CTEwhereId=@nodeunionallselectb.Id,b.Name,a.lv+1fromtemp_cteajoinRole_CTEbona.Id=b.ParentId)select*fromtemp_cte

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

declare@nodeintdeclare@numintset@node=3;set@num=1;withtemp_cteas(selectId,Name,0lv--查询出“根节点”，即指定的起始节点fromRole_CTEwhereId=@nodeunionallselectb.Id,b.Name,a.lv+1fromtemp_cteajoinRole_CTEbona.Id=b.ParentIdanda.lv<@num--控制递归层数)select*fromtemp_cte

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

使用普通 sql 语句实现：

declare@levelintdeclare@nodeintdeclare@numintdeclare@ResTabtable(nodeintnotnull,lvintnotnull)set@level=0--表示初始的等级set@node=8--表示初始的节点ID,即从指定的哪个节点开始查找set@num=2--指定递归层级，即循环的次数while(@level<=@numand@nodeisnotnull)--如果为空就表示没有查到父级了begininsertinto@ResTabselect@node,@levelset@level=@level+1select@node=ParentIdfromRole_CTEwhereId=@nodeendselecta.node,b.Name,a.lvfrom@ResTabaleftjoinRole_CTEbona.node=b.Id

使用 SQL CTE 实现：

declare@nodeintdeclare@numintset@node=8;set@num=2;withtemp_cteas(selectId,Name,ParentId,0lv--查询出“根节点”，即指定的起始节点fromRole_CTEwhereId=@nodeunionallselectb.Id,b.Name,b.ParentId,a.lv+1fromtemp_cteajoinRole_CTEbona.ParentId=b.Idanda.lv<@num--控制递归层数)select*fromtemp_cte

关于SQL Server中怎么利用公用表表达式实现递归就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。