SQL Server2005使用CTE实现递归

水深无声 2022-07-03 12:57 183阅读 0赞

CTE递归原理:

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

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

\-- 需要查询字段集合,也是查询结果的字段集合   
 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的下属报告的所有员工.

代码清单:

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

\-- 创建表   
 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

查询结果:

![.png][]

递归过程开始于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.

代码清单:

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

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** )

结果如下:

![2.png][]

还会看到以下错误:

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

代码清单:

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

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递归查询完成了.

[ContractedBlock.gif]: http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20220618/e4323d0ef65e4c79b5d4cf2dc170ffde.png
[.png]: /images/20220618/ce157ca9a9884e89af3f62929ebb45ad.png
[2.png]: /images/20220618/ee0ff330bc7c4ae7a01dd04938f11af5.png