今天基本搞清楚SQL Server中递归查询的实现方式，So，先记录下来。不过呢，个人觉得SQL Server的递归查询相对于Oracle中的递归查询更加难以理解。

从SQL Server 2005开始，我们可以直接通过CTE来支持递归查询，这对查询树形或层次结构的数据很有用。CTE即公用表表达式，虽然不恰当，但你可以将它看做成一个临时命名的结果集合。

我们先建立一个示例表，名称为MENU，表示菜单的层次结构:

    CREATETABLEMENU ( namenvarchar(50)NOTNULLPRIMARYKEY, seniornvarchar(50)NULL);  INSERTINTOMENUvalues('文件',NULL), ('新建','文件'), ('项目','新建'), ('使用当前连接查询','新建');

表示的菜单层次如下：

    文件 新建 项目 使用当前连接查询 OK，先看CTE的语法： WITHCTE名称(字段列表) AS ( 查询语句 )

例如：

    WITHlmenu(name,senior) as ( SELECTname,seniorfrommenu )

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

    SELECT*FROMlmenu

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

    WITHlmenu(name,senior,level)as( SELECTNAME,SENIOR,0levelFROMMENUWHERESENIORISNULLUNIONALLSELECTA.NAME,A.SENIOR,b.level+1FROMMENUA,lmenub wherea.senior=b.name) SELECT*fromlmenu

结果：

    nameseniorlevel ---------------------------------------------- 文件NULL0 新建文件1 使用当前连接查询新建2 项目新建2

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

    SELECTNAME,SENIOR,0levelFROMMENUWHERESENIORISNULL

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

    SELECTA.NAME,A.SENIOR,b.level+1FROMMENUA,lmenub wherea.senior=b.name

称之为递归成员，其特征为from子句中引用了CTE对象自身。

递归CTE具有一些限制条件(引自MSDN)：

至少有一个定位点成员和一个递归成员，当然，你可以定义多个定位点成员和递归成员，但所有定位点成员必须在递归成员的前面

定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符，最后一个定位点成员与递归成员之间必须使用UNION ALL，递归成员之间也必须使用UNION ALL连接

定位点成员和递归成员中的字段数量和类型必须完全一致

递归成员的FROM子句只能引用一次CTE对象

递归成员中不允许出现下列项

    SELECTDISTINCT GROUPBY HAVING 标量聚合 TOP LEFT、RIGHT、OUTERJOIN（允许出现INNERJOIN） 子查询

CTE递归查询的执行方式：

递归的终止依赖于定位点成员的，如果理解了这一点，也就理解了递归查询的执行方式。

我们来看上例的执行执行过程：

    SELECT*FROMlmenu

这条语句进入递归查询

    SELECTA.NAME,A.SENIOR,b.level+1FROMMENUA,lmenub wherea.senior=b.name

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

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

第一条记录：

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

    selectname,senior,0levelfrommenuwhereseniorisnullandname='文件'

    nameseniorlevel -------------------------------------------------- 文件NULL0

第二条记录：

即NAME = ‘新建’, 定位点成员结果集中没有该记录，将进入递归：

将当前行的值带入递归成员：

    SELECTA.NAME,A.SENIOR,b.level+1levelFROMMENUA,lmenub wherea.senior=b.name ANDa.senior='文件' ANDa.name='新建'

由于递归的关联条件是a.senior = b.name，所以b.name=’文件’，以此条件进入下级递归,这实际上就是第一条记录的情况，由于name=’文件’符合定位点条件，所以将终止递归，如果我们用子查询来替换掉lmenu递归成员，第二条记录的查询语句实际为：

    SELECTa.name,a.senior,b.level+1frommenua,( selectname,senior,0levelfrommenuwhereseniorisnullandname='文件')b wherea.senior=b.nameanda.senior='文件'anda.name='新建'

name senior level ——————— ————————– 新建 文件 1

第三条记录：

NAME=’使用当前连接查询’，同样不符合定位点条件，将进入递归：

    SELECTA.NAME,A.SENIOR,b.level+1FROMMENUA,lmenub wherea.senior=b.name ANDa.senior='新建' ANDa.name='使用当前连接查询'

同样，代入当前记录条件，下级递归b.name=’新建’,由于’新建’还不符合定位点条件，所以还将继续递归，及lmenu b表示子查询：

    selectc.name,c.senior,d.level+1levelfrommenuc,lmenud wherec.senior=d.name andc.name='新建' andc.senior='文件'

替换成上述语句后，d.name=’文件’，将再次判断是否需要继续递归，由于’文件’符合终止递归条件，所以将终止递归。

我们用子查询表示第三条记录的递归过程如下：

    SELECTa.name,a.senior,b.level+1levelFROMmenuA,( selectc.name,c.senior,d.level+1levelfrommenuc,( selectname,senior,0levelfrommenuwhereseniorisnullandname='文件')d wherec.senior=d.nameandc.name='新建'andc.senior='文件' )b wherea.senior=b.nameanda.senior='新建'anda.name='使用当前连接查询'

name senior level

——————————————————— ———–

使用当前连接查询 新建 2

第四条记录与第三条记录的递归层次完全一样。

原文标题：SQL Server 2008中的CTE递归查询

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

【编辑推荐】

    SQL Server 2000删除实战演习SQL Server存储过程的命名标准如何进行？卸载SQL Server 2005组件的正确顺序对SQL Server字符串数据类型的具体描述SQL Server数据类型的种类与应用