乐泰花艺培训学校

--站内查询--

文章排行榜
推荐课程
作品共享
网站开发
详解SQL Server 2008中CTE递归查询的实现
作者:乐泰    编辑:hew786    来自:乐泰艺术培训学校    浏览:942次    发布:2010/10/20 [ 字号: ]

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

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

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

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

表示的菜单层次如下:

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

例如:

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

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

  1. SELECT * FROM lmenu 

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

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

结果:

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

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

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

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

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

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

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

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

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

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

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

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

  1. SELECT DISTINCT   
  2. GROUP BY   
  3. HAVING   
  4. 标量聚合   
  5. TOP   
  6. LEFTRIGHTOUTER JOIN(允许出现 INNER JOIN)   
  7. 子查询 

CTE递归查询的执行方式:

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

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

  1. SELECT * FROM lmenu 

这条语句进入递归查询

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

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

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

第一条记录:

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

  1. select name,senior,0 level from menu where senior is null and name='文件' 

  1. name                    senior           level   
  2. --------------- ------------------------ -----------   
  3. 文件                      NULL                0 

第二条记录:

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

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

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

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

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

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

第三条记录:

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

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

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

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

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

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

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

name                                     senior                    level

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

使用当前连接查询                    新建                       2

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

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


推荐信息
评论(1)条
评论姓名:  
 
  • uso_cialis -- 2017/10/6 2:01:52
    I  toss-up  uso  cialis  prezzo  in  italia  most  the  cholesterol  for  cialis  prezzo  in  italia  risk  often  not.