递归SQL简单学习

递归查询解决树形数据很管用

SQL Server引入公用表表达式（CTE）可创建递归查询

首先，查看了一下MSDN关于CTE的介绍：

https://msdn.microsoft.com/zh-cn/library/ms186243.aspx

了解到如下信息：

CTE 的基本语法结构如下：
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

CTE的优点是可以引用自身，从而可以创建递归CTE。递归查询通常返回分层数据。

● 递归 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 的结果。

看了一下作者：xfrog的一篇关于CTE递归的博客

http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html

借用该文的例子：

CREATE TABLE MENU    (      name NVARCHAR(50) NOT NULL                        PRIMARY KEY ,      senior NVARCHAR(50) NULL    ); INSERT  INTO MENUVALUES  ( '文件', NULL ),        ( '新建', '文件' ),        ( '项目', '新建' ),        ( '使用当前连接查询', '新建' );

创建如上表，并添加树形数据

我们希望对数据形成一个树形的查询，这时就需要使用递归SQL

WITH    temp          AS ( SELECT   name ,                        senior ,                        0 AS level               FROM     MENU               WHERE    senior IS NULL               UNION ALL               SELECT   MENU.name ,                        MENU.senior ,                        level + 1               FROM     dbo.MENU                        INNER JOIN temp ON dbo.MENU.senior = temp.name             )    SELECT  name AS '名称' ,            senior AS '子项' ,            temp.level AS '层级'    FROM    temp

这是我的写法,查询结果如下：

我们可以发现，通过定位点成员，也就是这句SQL，

SELECT   name ,                        senior ,                        0 AS level               FROM     MENU               WHERE    senior IS NULL

我们可以查询这个树的叶子节点，就是这条数据

然后通过递归成员，就是这段SQL，

               SELECT   MENU.name ,                        MENU.senior ,                        level + 1               FROM     dbo.MENU                        INNER JOIN temp ON dbo.MENU.senior = temp.name

调用了这个名为temp的公用表，实现的递归。通过在Menu表中找到满足条件senior为temp的name的数据。对于本例，简单的可以理解，找到所有senior=‘文件’的数据。

结合实例，可以较好的理解递归写法。