SQL Server 中的 CTE 递归查询

从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）    
    子查询