详解SQLServer2008中CTE递归查询的实现

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

创新互联建站是一家专业提供含山企业网站建设,专注与网站建设、成都网站建设、H5开发、小程序制作等业务。10年已为含山众多企业、政府机构等服务。创新互联专业网站设计公司优惠进行中。

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

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

 
 
 
    
  
  
  
  1. CREATE TABLE MENU   
    2. 
  
  
  
  2.  (  
    3. 
  
  
  
  3.     name nvarchar(50) NOT NULL PRIMARY KEY,  
    4. 
  
  
  
  4.     senior nvarchar(50) NULL 
    5. 
  
  
  
  5. );  
    6. 
  
  
  
  6.    
    7. 
  
  
  
  7.  INSERT INTO MENU values 
    8. 
  
  
  
  8.     ('文件',NULL),  
    9. 
  
  
  
  9.     ('新建','文件'),  
    10. 
  
  
  
  10.     ('项目','新建'),  
    11. 
  
  
  
  11.     ('使用当前连接查询','新建'); 
    12.

表示的菜单层次如下：

 
 
 
    
  
  
  
  1. 文件   
    2. 
  
  
  
  2.     新建   
    3. 
  
  
  
  3.         项目   
    4. 
  
  
  
  4.         使用当前连接查询  
    5. 
  
  
  
  5.  
    6. 
  
  
  
  6. OK，先看CTE的语法：   
    7. 
  
  
  
  7.   WITH CTE名称(字段列表)   
    8. 
  
  
  
  8.   AS   
    9. 
  
  
  
  9.   (   
    10. 
  
  
  
  10.     查询语句   
    11. 
  
  
  
  11.   ) 
    12.

例如：

 
 
 
    
  
  
  
  1. WITH lmenu(name,senior)   
    2. 
  
  
  
  2. as   
    3. 
  
  
  
  3. (   
    4. 
  
  
  
  4.     SELECT name,senior from menu   
    5. 
  
  
  
  5. ) 
    6.

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

 
 
 
    
  
  
  
  1. SELECT * FROM lmenu 
    2.

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

 
 
 
    
  
  
  
  1. WITH lmenu(name,senior,level) as 
    2. 
  
  
  
  2.  (  
    3. 
  
  
  
  3.     SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL 
    4. 
  
  
  
  4.     UNION ALL 
    5. 
  
  
  
  5.     SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b  
    6. 
  
  
  
  6.     where a.senior = b.name 
    7. 
  
  
  
  7. )  
    8. 
  
  
  
  8.  
    9. 
  
  
  
  9. SELECT *  from lmenu 
    10.

结果：

 
 
 
    
  
  
  
  1. name               senior              level   
    2. 
  
  
  
  2. ----------------------------------- -----------   
    3. 
  
  
  
  3. 文件                NULL                 0   
    4. 
  
  
  
  4. 新建                文件                  1   
    5. 
  
  
  
  5. 使用当前连接查询      新建                 2   
    6. 
  
  
  
  6. 项目                新建                 2 
    7.

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

 
 
 
    
  
  
  
  1. SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL 
    2.

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

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

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

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

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

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

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

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

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

 
 
 
    
  
  
  
  1. SELECT DISTINCT   
    2. 
  
  
  
  2. GROUP BY   
    3. 
  
  
  
  3. HAVING   
    4. 
  
  
  
  4. 标量聚合   
    5. 
  
  
  
  5. TOP   
    6. 
  
  
  
  6. LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）   
    7. 
  
  
  
  7. 子查询 
    8.

CTE递归查询的执行方式：

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

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

 
 
 
    
  
  
  
  1. SELECT * FROM lmenu 
    2.

这条语句进入递归查询

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

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

 
 
 
    
  
  
  
  1. name                                       senior   
    2. 
  
  
  
  2. --- --------------------------------------------------   
    3. 
  
  
  
  3. 文件                                          NULL   
    4. 
  
  
  
  4. 新建                                            文件   
    5. 
  
  
  
  5. 使用当前连接查询                       新建   
    6. 
  
  
  
  6. 项目                                            新建 
    7.

第一条记录：

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

 
 
 
    
  
  
  
  1. select name,senior,0 level from menu where senior is null and name='文件' 
    2. 
 
 
 
 
 
 
    
  
  
  
  1. name                    senior           level   
    2. 
  
  
  
  2. --------------- ------------------------ -----------   
    3. 
  
  
  
  3. 文件                      NULL                0 
    4.

第二条记录：

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

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

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

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

 
 
 
    
  
  
  
  1. SELECT a.name,a.senior,b.level+1 from menu a, (  
    2. 
  
  
  
  2.   select name,senior,0 level  from menu 
    3. 
  
  
  
  3. where senior is null and name='文件' 
    4. 
  
  
  
  4. ) b  
    5. 
  
  
  
  5. where a.senior=b.name 
    6. 
  
  
  
  6.     and a.senior = '文件' 
    7. 
  
  
  
  7.     and a.name='新建' 
    8.

name            senior                    level
--------------------- --------------------------
新建             文件                           1

第三条记录：

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

 
 
 
    
  
  
  
  1. SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b   
    2. 
  
  
  
  2.     where a.senior = b.name   
    3. 
  
  
  
  3.           AND a.senior = '新建'   
    4. 
  
  
  
  4.           AND a.name = '使用当前连接查询' 
    5.

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

 
 
 
    
  
  
  
  1. select c.name,c.senior,d.level+1 level from menu c,lmenu d   
    2. 
  
  
  
  2. where c.senior = d.name   
    3. 
  
  
  
  3.      and c.name = '新建'   
    4. 
  
  
  
  4.      and c.senior = '文件' 
    5.

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

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

 
 
 
    
  
  
  
  1. SELECT a.name,a.senior,b.level+1 level FROM menu A,(  
    2. 
  
  
  
  2.         select c.name,c.senior,d.level+1 level from menu c,(  
    3. 
  
  
  
  3.           select name,senior,0 level from menu where senior is null and name='文件' 
    4. 
  
  
  
  4.     ) d  
    5. 
  
  
  
  5.     where c.senior = d.name 
    6. 
  
  
  
  6.         and c.name = '新建' 
    7. 
  
  
  
  7.         and c.senior = '文件'     
    8. 
  
  
  
  8. ) b  
    9. 
  
  
  
  9. where a.senior = b.name 
    10. 
  
  
  
  10.     and a.senior = '新建' 
    11. 
  
  
  
  11.     and a.name = '使用当前连接查询' 
    12.

name                                     senior                    level

--------------------------------------------------------- -----------

使用当前连接查询                    新建                       2

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

当前标题：详解SQLServer2008中CTE递归查询的实现
网页地址：http://www.shufengxianlan.com/qtweb/news33/267883.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

python知识

行业网站建设

雷运建站　　 　教育网站设计方案　　 　企业网站建设　　 　四川艺考培训　　 　钢筋机械设备　　 　响应式建站　　 　平昌做网站　　 　注册域名　　 　成都翻译公司　　 　温江网站制作　　 　香港服务器空间　　 　seo优化排名　　 　重庆网站建设　　 　成都机房机柜租用　　 　消防维保　　 　安防网站建设方案　　 　成都艾瑞格　　 　百度推广公司　　 　成都网站推广　　 　成都网站维护