请教Sqlserver 2008中使用with as语句递归查询的问题。

Question

with cte as
(
select Id,Pid,DeptName,0 as lvl from Department
where Id = 2
union all
select d.Id,d.Pid,d.DeptName,lvl+1 from cte c inner join Department d
on c.Id = d.Pid
)
select * from cte
--------------------------------------------------- 以上为SQL语句

Id Pid DeptName
----------- ----------- -------------------------------
1 0 总部
2 1 研发部
3 1 测试部
4 1 质量部
5 2 小组1
6 2 小组2
7 3 测试1
8 3 测试2
9 5 前端组
10 5 美工
---------------------------------------------------- 以上为表结构

Id Pid DeptName lvl
----------- ----------- -------------------------------------------------- -----------
2 1 研发部 0
5 2 小组1 1
6 2 小组2 1
9 5 前端组 2
10 5 美工 2
-------------------------------------------------------------------以上为结果集

那么请教，这条语句的执行过程是怎么样的。 =。=
特别是from cte这里。重复调用cte应该会死循环啊= =，是什么条件让这个递归退出了？？？
以上内容来自博客园博主忧忧夏天。如有冒犯请原谅=。=。

Answer 1

Transact-SQL 中的递归 CTE 的结构与其他编程语言中的递归例程相似。尽管其他语言中的递归例程返回标量值，但递归 CTE 可以返回多行。
递归 CTE 由下列三个元素组成：
例程的调用。
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。
CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。
例程的递归调用。
递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。
终止检查。
终止检查是隐式的；当上一个调用中未返回行时，递归将停止。
如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，
可以通过在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之间的值，来限制特定语句允许的递归级数。有关详细信息，请参阅查询提示 (Transact-SQL) 和 WITH common_table_expression_r(Transact-SQL)。

伪代码和语义
递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
递归执行的语义如下：
将 CTE 表达式拆分为定位点成员和递归成员。
运行定位点成员，创建第一个调用或基准结果集 (T0)。
运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。
重复步骤 3，直到返回空集。
返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。

If the MAXRECURSION query hint is not specified, the default limit is 100.追问

不怎么看得懂。。。

追答

默认是设置最大递归为100，不会让你死循环的。

追问

最大为100我知道，但我不明白这个语句的执行过程= =。
比如第一次执行到递归成员。 from cte时。SQLServer会继续调用cte。 然后cte又继续调用cte...完全看不明白在哪里退出了。。。

追答

你学过其他语言中的递归函数的写法吗。
找不到就退出，回到原位
WITH cte (EmployeeID, ManagerID, Title) as
(
......
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT *
FROM cte
OPTION (MAXRECURSION 2); 这里就限制了二级，而默认可不写，为100

追问

java c++之类的高级语言函数递归很容易理解啊，但这个我始终看不明白。
比如select * from cte之后是第一次执行cte，首先执行定位点成员：
返回
1 0 总部
之后执行递归成员内链接查询。
这个时候又会执行cte.. cte并没有返回结果啊= =。 这不是死循环了吗。

本回答被提问者和网友采纳