递归查询原理

CTE的递归查询必须满足三个条件：初始条件，递归调用表达式，终止条件；CTE 递归查询的伪代码如下：

with cte_name as(
    查询语句
    
    union all
    
    查询语句
)
select * from cte_name

1. 递归查询至少包含两个子查询：

• 第一个子查询称作定点（Anchor）子查询：定点查询只是一个返回有效表的查询，用于设置递归的初始值；
• 第二个子查询称作递归子查询：该子查询调用CTE名称，触发递归查询，实际上是递归子查询调用递归子查询；
• 两个子查询使用union all，求并集；

2. CTE的递归终止条件

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

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

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

3. 递归步骤

step1：定点子查询设置CTE的初始值，即CTE的初始值Set0；

递归调用的子查询过程：递归子查询调用递归子查询；

step2：递归子查询第一次调用CTE名称，CTE名称是指CTE的初始值Set0，第一次执行递归子查询之后，CTE名称是指结果集Set1；

step3：递归子查询第二次调用CTE名称，CTE名称是指Set1，第二次执行递归子查询之后，CTE名称是指结果集Set2；

step4：在第N次执行递归子查询时，CTE名称是指Set(N-1)，递归子查询都引用前一个递归子查询的结果集；

Step5：如果递归子查询返回空数据行，或超出递归次数的最大限制，停止递归；

查询示例

创建测试数据

create table Product (
  FID varchar(20),
  DeptID varchar(20),
  DeptName varchar(50)
)
go
insert into
  dbo.Product (FID, DeptID, DeptName)
values
  ('0', '999', '生产部'),
  ('999', '998', '冲压车间'),
  ('999', '997', '组装车间'),
  ('997', '996', '组装1部'),
  ('997', '995', '组装2部'),('0', '1001', '销售部'),
  ('998', '998001', '冲压1部'),
  ('996', '997996001', '组装1部一组'),
  ('1001', '1001001', '销售一组'),
  ('1001', '1001002', '销售二组')

View Code

;with cte as(
    select FID, DeptID, DeptName, cast(DeptName as varchar(max)) as ReportPath, cast(row_number() over(order by FID) as varchar(max)) as LevelNum from dbo.Product
    where FID=0
    
    union all
    select B.FID, B.DeptID, B.DeptName, B.DeptName+'-->'+A.ReportPath as ReportPath, LevelNum+'.'+cast(row_number() over(order by B.FID) as varchar(max)) as LevelNum from cte A
    inner join dbo.Product B on A.DeptID=B.FID
)

select * from cte order by cte.LevelNum

step1：查询FID=0，作为root node，这是递归查询的起始点。

step2：迭代公式是 union all 下面的查询语句。在查询语句中调用中cte，而查询语句就是cte的组成部分，即 “自己调用自己”，这就是递归的真谛所在。

所谓迭代，是指每一次递归都要调用上一次查询的结果集，Union ALL是指每次都把结果集并在一起。

step3-N，迭代公式利用上一次查询返回的结果集执行特定的查询，直到CTE返回null 或达到最大的迭代次数，默认值是32。最终的结果集是迭代公式返回的各个结果集的并集，求并集是由Union All 子句定义的，并且只能使用Union ALL。

 

原文链接：https://blog.csdn.net/3150379/article/details/54865603