递归 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 的结果
WITH lmenu(deptid,up_dept) as
(
SELECT deptid,up_dept FROM dept WHERE up_dept='CN06000168'
union ALL
SELECT A.deptid,A.up_dept FROM dept A,lmenu b
where a.up_dept = b.deptid
)
SELECT * from lmenu --order by level
option(maxrecursion number)
例子2:查询CN06000168所有的父部门:
WITH lmenu(deptid,up_dept) as
(
SELECT deptid,up_dept FROM dept WHERE up_dept='CN06000168'
union ALL
SELECT A.deptid,A.up_dept FROM dept A,lmenu b
where a.deptid = b.up_dept
)
SELECT * from lmenu --order by level
<pre name="code" class="sql"> option(maxrecursion number)