SQL Server2005使用CTE实现递归-阿里云开发者社区

开发者社区> 数据库> 正文

SQL Server2005使用CTE实现递归

简介:

CTE递归原理:

     递归CTE是由两个最小查询构建的.第一个是定位成员(Anchor Member,AM),它是一个非递归查询,第二个是递归成员(Recursive Member,RM),它是递归查询.在CTE括号中(AS 子句之后),定义独立查询或引用回相同CTE的查询,AM与RM由UNION ALL语句分隔.AM紧被调用一次,RM将被重复调用,直到查询不在返回数据行为止.可以使用UNION或UNION ALL运算符彼此追加多个AM,具体取决于是否希望删除重复数据(必须使用UNION ALL运算符来追加递归成员).语法如下:

代码
--需要查询字段集合,也是查询结果的字段集合
WITH SimpleRecursive(filed names)
AS
(
    
--查询定位成员表
    <SELECT Statement for the Anchor Member>
    
UNION ALL
    
--查询递归成员表
    <SELECT Statement for the Recursive Member>
)
SELECT * FROM SimpleRecursive

 

实例:我们将创建一个员工表和一个名为ReportsTo的自引用字段,其引用回Emloyee_ID,然后编写一个查询,其返回像Stephen(Employee_ID=2)报告的所有员工以及向Stephen的下属报告的所有员工.

代码清单:

代码
--创建表
CREATE TABLE Employee_Tree
    (
      Employee_NM 
NVARCHAR(50) ,
      Employee_ID 
INT PRIMARY KEY ,
      ReportsTo 
INT
    )

INSERT  INTO Employee_Tree
VALUES  ( 'Richard'1NULL )
INSERT  INTO Employee_Tree
VALUES  ( 'Stephen'21 )
INSERT  INTO Employee_Tree
VALUES  ( 'Clemens'32 )
INSERT  INTO Employee_Tree
VALUES  ( 'Malek'42 )
INSERT  INTO Employee_Tree
VALUES  ( 'Goksin'54 )
INSERT  INTO Employee_Tree
VALUES  ( 'Kimberly'61 )
INSERT  INTO Employee_Tree
VALUES  ( 'Ramesh'75 )
--创建递归查询
WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo )
          
AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo
               
FROM     Employee_Tree
               
WHERE    Employee_ID = 2
               
UNION ALL
               
SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo
               
FROM     Employee_Tree p
                        
INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo
             )
    
SELECT  sr.Employee_ID AS empid ,
            sr.Employee_NM 
AS Emp ,
            et.Employee_NM 
AS Boss
    
FROM    SimpleRecursive sr
            
INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID

 

查询结果:

递归过程开始于Employee_ID=2的位置(定义成员或第一个SELECT).它获取该记录,并使用递归递归成员(UNION ALL之后的SELECT)获取所有向Stephen报告的记录以及该记录的子记录.(Goksin向Malek报告,Malek向Stephen报告).每一个后续递归都尝试找到更多的子记录.它们以先前递归已经找到的员工作为父记录.最终该递归不返回结果,这是导致递归停止的原因(为什么没有返回Kimberly的原因).如果该定位成员被改变成Employee_ID=1,则Kimberly也将在结果中被返回.

     根据设计,递归成员将一直查找子记录,并可以无限循环.如果您怀疑将会进行许多循环,并希望限制递归调用次数,可以使用OPTION子句在外部查询的后面指定MAXRECURSION选项.

OPTION(MAXRECURSION 25)

这一选项将使sql server2005在cte出现指定限制时产生一条错误.默认情况下,限制为100(即省略该选项时).要不想指定选项.必须将MAXRECURSION设为0.

代码清单:

 

代码
WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo )
          
AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo
               
FROM     Employee_Tree
               
WHERE    Employee_ID = 2
               
UNION ALL
               
SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo
               
FROM     Employee_Tree p
                        
INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo
             )
    
SELECT  sr.Employee_ID AS empid ,
            sr.Employee_NM 
AS Emp ,
            et.Employee_NM 
AS Boss
    
FROM    SimpleRecursive sr
            
INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID
            
OPTION(MAXRECURSION 2)

 

 结果如下:

还会看到以下错误:

消息 530,级别 16,状态 1,第 1 行
语句被终止。完成执行语句前已用完最大递归 2。
    一种可以避免出现此异常的方法是使用一个所产生的列来跟踪所在的级别,并包含在WHERE子句中,而不是MAXRECURSION.以下结果与上一示例相同,但不产生错误.

代码清单:

代码
WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo ,Sublevel)
          
AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo,
                        
0
               
FROM     Employee_Tree
               
WHERE    Employee_ID = 2
               
UNION ALL
               
SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo,
                        Sublevel 
+ 1
               
FROM     Employee_Tree p
                        
INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo
             )
    
SELECT  sr.Employee_ID AS empid ,
            sr.Employee_NM 
AS Emp ,
            et.Employee_NM 
AS Boss      
    
FROM    SimpleRecursive sr
            
INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID
            
WHERE    Sublevel <=2

 

ok,CTE递归查询完成了.

 

 

本文转自温景良(Jason)博客园博客,原文链接:http://www.cnblogs.com/wenjl520/archive/2010/01/18/1650393.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章