SQL Server 2008中的CTE递归查询

递归 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

递归执行的语义如下：

1. 将 CTE 表达式拆分为定位点成员和递归成员。

2. 运行定位点成员，创建第一个调用或基准结果集 (T0)。

3. 运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。

4. 重复步骤 3，直到返回空集。

5. 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果
   

例子1：查询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.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)