SQL Server 2008中的CTE递归查询

网络整理 - 07-27

个人觉得sql server的递归查询相对于Oracle中的递归查询更加难以理解。从SQL Server 2005开始，我们可以直接通过CTE来支持递归查询，这对查询树形或层次结构的数据很有用。CTE即公用表表达式，虽然不恰当，但你可以将它看做成一个临时命名的结果集合。
我们先建立一个示例表，名称为MENU，表示菜单的层次结构:

CREATE TABLE MENU
(
name nvarchar(50) NOT NULL PRIMARY KEY,
senior nvarchar(50) NULL
);

INSERT INTO MENU values
('文件',NULL),
('新建','文件'),
('项目','新建'),
('使用当前连接查询','新建');

表示的菜单层次如下：

文件
新建
项目
使用当前连接查询

OK，先看CTE的语法：
WITH CTE名称(字段列表)
AS
(
查询语句
)
例如：
WITH lmenu(name,senior)
as
(
SELECT name,senior from menu
)

我们定义了一个名称为lmenu的CTE，这样我们后续即可直接使用lmenu来查询，如：
SELECT * FROM lmenu

如果我们在定义CTE的查询语句中直接引用CTE表本身，则会形成递归查询，当然递归查询具有自己的特殊结构，下面的SQL通过递归查询获取每个菜单的层次深度：

WITH lmenu(name,senior,level) as
(
SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL
UNION ALL
SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
where a.senior = b.name
)

SELECT * from lmenu

结果：

name senior level
-------------------------------------------------- -------------------------------------------------- -----------
文件 NULL 0
新建文件 1
使用当前连接查询新建 2
项目新建 2

注意查询定义语句，它由两条查询语句构成，其中

SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL

称为定位成员，SQL Server通过此语句来判断是否继续进行递归。
语句

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
where a.senior = b.name

称之为递归成员，其特征为from子句中引用了CTE对象自身。
递归CTE具有一些限制条件(引自MSDN)：

至少有一个定位点成员和一个递归成员，当然，你可以定义多个定位点成员和递归成员，但所有定位点成员必须在递归成员的前面
定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符，最后一个定位点成员与递归成员之间必须使用UNION ALL，递归成员之间也必须使用UNION ALL连接
定位点成员和递归成员中的字段数量和类型必须完全一致
递归成员的FROM子句只能引用一次CTE对象
递归成员中不允许出现下列项
SELECT DISTINCT
GROUP BY
HAVING
标量聚合
TOP
LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）
子查询

CTE递归查询的执行方式：
递归的终止依赖于定位点成员的，如果理解了这一点，也就理解了递归查询的执行方式。
我们来看上例的执行执行过程：

SELECT * FROM lmenu

这条语句进入递归查询

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
where a.senior = b.name

作为最外层的语句，显然递归的第一层应该根据MENU表的记录来循环(如果查询执行计划，这表示一个嵌套循环)，假设menu表中查询出的记录顺序如下：

name senior
-------------------------------------------------- --------------------------------------------------
文件 NULL
新建文件
使用当前连接查询新建
项目新建

第一条记录：
首先判断是否进入递归，由于文件包含在定位点成员结果集中，不符合递归条件，所以不进入递归，直接返回从定位点成员集合中返回记录：

select name,senior,0 level from menu where senior is null and

name senior level
-------------------------------------------------- -------------------------------------------------- -----------
文件 NULL 0

第二条记录：
即NAME = '新建', 定位点成员结果集中没有该记录，将进入递归：
将当前行的值带入递归成员：

SELECT A.NAME,A.SENIOR,b.level+1 level FROM MENU A,lmenu b
where a.senior = b.name
AND a.senior = '文件'
AND a.name='新建'