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', 1, NULL )
INSERT  INTO Employee_Tree
VALUES  ( 'Stephen', 2, 1 )
INSERT  INTO Employee_Tree
VALUES  ( 'Clemens', 3, 2 )
INSERT  INTO Employee_Tree
VALUES  ( 'Malek', 4, 2 )
INSERT  INTO Employee_Tree
VALUES  ( 'Goksin', 5, 4 )
INSERT  INTO Employee_Tree
VALUES  ( 'Kimberly', 6, 1 )
INSERT  INTO Employee_Tree
VALUES  ( 'Ramesh', 7, 5 )
--创建递归查询
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递归查询完成了.