SQLServerCTE递归查询全解.pdf

SQLServerCTE递归查询全解 在TSQL脚本中，也能实现递归查询，SQL Server提供CTE （Common Table Expression），只需要编写少量的代码，就能实现递归查询， 本⽂详细介绍CTE递归调⽤的特性和使⽤⽰例，递归查询主要⽤于层次结构的查询，从叶级（Leaf Level）向顶层（Root Level）查询，或 从顶层向叶级查询，或递归的路径（Path）。 ⼀，递归查询原理 CTE的递归查询必须满⾜三个条件：初始条件，递归调⽤表达式，终⽌条件，CTE 递归查询的伪代码如下： WITH cte_name ( column_name [,...n] ) AS ( --Anchor member is defined CTE_query_definition UNION ALL --Recursive member is defined referencing cte_name CTE_query_definition ) -- Statement using the CTE 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 ：如果递归⼦查询返回空数据⾏，或超出递归次数的最⼤限制，停⽌递归； ⼆，递归查询⽰例（员⼯职称） 1，创建测试数据 ManagerID是UserID的⽗节点，这是⼀个⾮常简单的层次结构模型。 use tempdb go create table dbo.dt_user ( UserID int, ManagerID int, Name Nvarchar(10) ) insert into dbo.dt_user select 1,-1,NBoss union all select 11,1,NA1 union all select 12,1,NA2 union all select 13,1,NA3 union all select 111,11,NB1 union all select 112,11,NB2 union all select 121,12,NC1 2，查询每个User的的直接上级Manager ;with cte as ( select UserID,ManagerID,name,name as ManagerName from dbo.dt_user where ManagerI