[SQL Server] TSQL实现SQL Server中CTE 递归查询

参考博客：https://www.cnblogs.com/ljhdo/p/4580347.html

简介

递归查询主要应用于层级结构表的查询：

• 叶节点-> 根节点的查询
• 根节点-> 叶节点的查询

递归查询必须满足的条件

初始条件

递归查询至少包含两个子查询：两个子查询使用union all，求并集

1. 第一个子查询称作定点（Anchor）子查询：定点查询只是一个返回有效表的查询，用于设置递归的初始值；
2. 第二个子查询称作递归子查询：触发递归查询，实际上是递归子查询调用递归子查询

终止条件

• 递归查询没有显式的递归终止条件，
• 只有当递归子查询返回空结果集（没有数据行返回）或是超出了递归次数的最大限制时，才停止递归。
• 默认的递归查询次数是100，可以使用查询提示（hint）：MAXRECURSION 控制递归的最大次数：OPTION( MAXRECURSION 16)；如果允许无限制的递归次数，使用查询提示：option(maxrecursion 0)；当递归查询达到指定或默认的 MAXRECURSION 数量限制时，SQL Server将结束查询并返回错误，如下：

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

• 事务执行失败，该事务包含的所有操作都被回滚。在产品环境中，慎用maxrecursion 查询提示，推荐通过 where 条件限制递归的次数。

递归调用表达式

1. 定点子查询设置CTE的初始值，即CTE的初始值Set0；递归调用的子查询过程：递归子查询调用递归子查询；
2. 递归子查询第一次调用CTE名称，CTE名称是指CTE的初始值Set0，第一次执行递归子查询之后，CTE名称是指结果集Set1；
3. 递归子查询第二次调用CTE名称，CTE名称是指Set1，第二次执行递归子查询之后，CTE名称是指结果集Set2；
4. 在第N次执行递归子查询时，CTE名称是指Set(N-1)，递归子查询都引用前一个递归子查询的结果集；
5. 如果递归子查询返回空数据行，或超出递归次数的最大限制，停止递归

递归查询示例

创建测试数据

创建一张临时表manager_user，其中ManagerID是UserID的父节点，UserID是ManagerID的子节点

create table #manager_user
(
    UserID int,
    ManagerID int,
    Name Nvarchar(10)
)

insert into #manager_user
select 1,-1,N'Boss'
union all
select 11,1,N'A1'
union all
select 12,1,N'A2'
union all
select 13,1,N'A3'
union all
select 111,11,N'B1'
union all
select 112,11,N'B2'
union all
select 121,12,N'C1'

其结果展示如下图所示：

查询每个User的的直接上级Manager

1. 查询ManagerID=-1，作为root node，这是递归查询的起始点。
2. 迭代公式是 union all 下面的查询语句。在查询语句中调用中cte，而查询语句就是cte的组成部分，即 “自己调用自己”，这就是递归的真谛所在。所谓迭代，是指每一次递归都要调用上一次查询的结果集，Union ALL是指每次都把结果集并在一起。
3. -N，迭代公式利用上一次查询返回的结果集执行特定的查询，直到CTE返回null 或达到最大的迭代次数，默认值是32。最终的结果集是迭代公式返回的各个结果集的并集，求并集是由Union All 子句定义的，并且只能使用Union ALL。

with cte as
(
select UserID,ManagerID,name,name as ManagerName
from #manager_user
where ManagerID=-1

union all

select c.UserID,c.ManagerID,c.Name,p.name as ManagerName
from cte P
inner join #manager_user c
    on p.UserID=c.ManagerID
)
select UserID,ManagerID,Name,ManagerName
from cte
order by UserID

其结果展示为：

查询路径，在层次结构中查询子节点到父节点的path

with cte as
(
select UserID,ManagerID,name,cast(name as nvarchar(max)) as ReportPath
from #manager_user
where ManagerID=-1

union all
select c.UserID,c.ManagerID,c.Name,c.name+'->'+p.ReportPath as ReportPath
from cte P
inner join #manager_user c
    on p.UserID=c.ManagerID
)
select UserID,ManagerID,Name,ReportPath
from cte
order by UserID

其结果展示为：

实现由父级向子级的查询

要想获取ManagerID的所有UserID等信息，必须使用递归查询

下列sql展示的是ManagerId =1的所有相关子节点

with cte(UserId,ManagerID,Name) as
(
select * 
from #manager_user
where UserId=1

union all
select h.* 
from #manager_user h
inner join cte c on h.ManagerID=c.UserId 
--where c.UserId!=h.UserId 
)
select *
from cte
order by ManagerID

其查询结果为：

如果要查看向内递归到多少level，可以使用派生列

下列sql展示的是ManagerId =1的所有相关子节点以及递归到多少level

with cte(UserId,ManagerId,Name,Level) as
(
select UserId,ManagerId,Name,0 as Level
from #manager_user 
where UserId=1

union all
select h.UserId,h.ManagerId,h.Name,c.Level+1 as Level
from #manager_user h
inner join cte c on h.ManagerId=c.UserId 
--where c.UserId!=h.UserId
)
select *
from cte
order by ManagerId

其结果展示为：

由子级向父级的递归查询

下列sql展示的是UserId =121的所有相关父节点

with cte as
(
select UserId,ManagerId,name
from #manager_user
where UserId= 121

union all
select h.UserId,h.ManagerId,h.name
from #manager_user h
inner join cte c on h.UserId=c.ManagerId
)
select UserId,ManagerId,name
from cte
order by ManagerId

其结果展示为：