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 MENU
VALUES ( '文件', 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=‘文件’的数据。
结合实例,可以较好的理解递归写法。