实例解析SQL SERVER CTE 递归查询

最新推荐文章于 2024-08-07 11:39:09 发布

envykok

最新推荐文章于 2024-08-07 11:39:09 发布

阅读量356

点赞数

分类专栏： SQL 经典问题文章标签： sql server tree null 2010

本文链接：https://blog.csdn.net/envykok/article/details/5651986

版权

SQL 经典问题专栏收录该内容

34 篇文章 0 订阅

订阅专栏

实例:

(来自：http://space.itpub.net/16436858/viewspace-625445）

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

代码清单:

表结构：

Employee_NM                                        Employee_ID ReportsTo
-------------------------------------------------- ----------- -----------
Richard                                            1           NULL
Stephen                                            2           1
Clemens                                            3           2
Malek                                              4           2
Goksin                                             5           4
Kimberly                                           6           1
Ramesh                                             7           5

代码

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

empid       Emp                                                Boss
----------- -------------------------------------------------- --------------------------------------------------
2           Stephen                                            Richard
3           Clemens                                            Stephen
4           Malek                                              Stephen
5           Goksin                                             Malek
7           Ramesh                                             Goksin

(5 row(s) affected)

解析：

（来自：http://tech.e800.com.cn/articles/2010/16/1262758981700_1.html）

递归CTE最少包含两个查询(也被称为成员)。第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点。第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

notes: from envykok

第一次递归：

SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo
               FROM     Employee_Tree
               WHERE    Employee_ID = 2

Employee_NM Employee_ID ReportsTo
-------------------------------------------------- ----------- -----------
Stephen 2 1

那么：

SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo
               FROM     Employee_Tree p
                        INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo

此时的SimpleRecursive =〉只有1 行

运行后，将增加两行

Employee_NM                                        Employee_ID ReportsTo
-------------------------------------------------- ----------- -----------
Clemens                                            3           2
Malek                                              4           2

此时的SimpleRecursive =〉只有3 行

依次类推。

递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。