今天基本搞清楚SQL Server中递归查询的实现方式，So，先记录下来 。不过呢，个人觉得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=文件
- 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=新建
由于递归的关联条件是a.senior = b.name，所以b.name=文件，以此条件进入下级递归,这实际上就是第一条记录的情况，由于name=文件符合定位点条件，所以将终止递归，如果我们用子查询来替换掉lmenu递归成员，第二条记录的查询语句实际为：
- SELECT a.name,a.senior,b.level+1 from menu a, (
- select name,senior,0 level from menu
- where senior is null and name=文件
- ) b
- where a.senior=b.name
- and a.senior = 文件
- and a.name=新建
name senior level
--------------------- --------------------------
新建 文件 1
第三条记录：
NAME=使用当前连接查询，同样不符合定位点条件，将进入递归：
- SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
- where a.senior = b.name
- AND a.senior = 新建
- AND a.name = 使用当前连接查询
同样，代入当前记录条件，下级递归b.name=新建,由于新建还不符合定位点条件，所以还将继续递归，及lmenu b表示子查询：
- select c.name,c.senior,d.level+1 level from menu c,lmenu d
- where c.senior = d.name
- and c.name = 新建
- and c.senior = 文件
替换成上述语句后，d.name=文件，将再次判断是否需要继续递归，由于文件符合终止递归条件，所以将终止递归 。
我们用子查询表示第三条记录的递归过程如下：
- SELECT a.name,a.senior,b.level+1 level FROM menu A,(
- select c.name,c.senior,d.level+1 level from menu c,(
- select name,senior,0 level from menu where senior is null and name=文件
- ) d
- where c.senior = d.name
- and c.name = 新建
- and c.senior = 文件
- ) b
- where a.senior = b.name
- and a.senior = 新建
- and a.name = 使用当前连接查询
name senior level
--------------------------------------------------------- -----------
使用当前连接查询 新建 2
第四条记录与第三条记录的递归层次完全一样 。
原文标题：SQL Server 2008中的CTE递归查询
链接：http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html
