用公共表达式 实现递归查询

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

递归查询通常用于返回分层数据（即遍历树结构）

递归 CTE 的结构(MSDN)

递归 CTE 由下列三个元素组成：

1. 例程的调用。
   递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。
   CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。
2. 例程的递归调用。
   递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。
3. 终止检查。
   终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

伪代码和语义

递归 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）递归 CTE 定义至少必须包含两个 CTE 查询定义（一个定位点成员和一个递归成员）。也可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。

（2）定位点成员必须（UNION ALL、UNION、INTERSECT 或 EXCEPT）之一结合使用。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 运算符。

（3）定位点成员和递归成员中的列数必须一致。

（4）递归成员中列的数据类型必须与定位点成员中相应列的数据类型兼容。

（5）递归成员的 FROM 子句只能引用一次 CTE expression_name。

（6）在递归成员的 CTE_query_definition 中不允许出现下列项：

（ DISTINCT、GROUP BY、HAVING、标量聚合、TOP、LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）、子查询）

（7）无论参与的 SELECT 语句返回的列的为空性如何，递归 CTE 返回的全部列都可以为空。

（8）如果递归 CTE 组合不正确，可能会导致无限循环。

（9）不能使用包含递归公用表表达式的视图来更新数据。

（10）可以使用 CTE 在查询上定义游标。递归 CTE 只允许使用快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。

（11）可以在 CTE 中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。

举例

创建表
CREATE TABLE TREES
(ID int ,ROOTS char(6),SUB char(6) )
--插入数据（即一个树结构）
INSERT INTO TREES SELECT 1,'A','B'
INSERT INTO TREES SELECT 2,'A','C'
INSERT INTO TREES SELECT 3,'B','D'
INSERT INTO TREES SELECT 4,'B','E'
INSERT INTO TREES SELECT 5,'C','F'
INSERT INTO TREES SELECT 6,'M','U'
INSERT INTO TREES SELECT 7,'M','N'
INSERT INTO TREES SELECT 8,'N','L'

数据如下：

ID  ROOTS  SUB

1    A         B    
2    A         C    
3    B         D    
4    B         E    
5    C         F    
6    M         U    
7    M         N    
8    N         L    

--遍历以A开头的整个树
WITH read_tree(ID,ROOTS,SUB)
AS
(--起始条件（基准结果集）
SELECT ID,ROOTS,SUB
FROM TREES
WHERE ROOTS='A'
UNION ALL
--递归条件
SELECT M.ID,M.ROOTS,M.SUB
FROM TREES AS M
INNER JOIN read_tree
ON  M.ROOTS = read_tree.SUB
)
SELECT * FROM read_tree

结果如下：

ID  ROOTS  SUB

1    A         B    
2    A         C    
5    C         F    
3    B         D    
4    B         E    

转载于:https://www.cnblogs.com/dyufei/archive/2009/11/16/2573969.html