logo标签列表

SQL Server中的递归CTE如何工作？

sqlsql-serverrecursioncommon-table-expression
5

有人能帮我理解这个递归CTE是如何工作的吗？

WITH
RECURSIVECTE (EMPID, FULLNAME, MANAGERID, [ORGLEVEL]) AS
    (SELECT EMPID,
            FULLNAME,
            MANAGERID,
            1
     FROM RECURSIVETBL
     WHERE MANAGERID IS NULL
     UNION ALL
     SELECT A.EMPID,
            A.FULLNAME,
            A.MANAGERID,
            B.[ORGLEVEL] + 1
     FROM RECURSIVETBL A
          JOIN RECURSIVECTE B ON A.MANAGERID = B.EMPID)
SELECT *
FROM RECURSIVECTE;
你能具体说明一下吗？你想要解释什么？你有问过编写rCTE的人吗？ - Thom A
首先请查看以下链接：[https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017] - DhruvJoshi
1
我想了解关于“B.[ORGLEVEL] + 1”部分的内容，我想知道它如何迭代以及如何知道定义组织级别。 - Vijay Thapa
2个回答
14

在SQL Server中，递归CTE有两个部分：

锚点：是您递归的起始点。它是一个集合，将通过递归连接进一步扩展。

SELECT 
    EMPID,
    FULLNAME,
    MANAGERID,
    1 AS ORGLEVEL
FROM 
    RECURSIVETBL
WHERE 
    MANAGERID IS NULL

似乎它正在获取所有没有任何经理的员工（可能是最高领导或树形关系中的根节点）。

递归：与UNION ALL相关联，该集合必须引用声明的CTE（从而使其递归）。想象一下，您将如何将锚点的结果扩展到下一级。

UNION ALL

SELECT 
    A.EMPID,
    A.FULLNAME,
    A.MANAGERID,
    B.[ORGLEVEL] + 1
FROM 
    RECURSIVETBL A
    JOIN RECURSIVECTE B  -- Notice that we are referencing "RECURSIVECTE" which is the CTE we are declaring
    ON A.MANAGERID = B.EMPID

在这个例子中，我们首先获取锚定结果集（所有没有经理的员工），并通过MANAGERID将它们与RECURSIVETBL连接起来，因此A.EMPID将保存先前选择的经理的员工。只要每个最后的结果集可以生成新的行，这个连接就会继续进行下去。
在递归部分上有一些限制（例如不能进行分组或另一个嵌套递归）。此外，由于它是以UNION ALL开头的，所以它的规则也适用（列数和数据类型必须匹配）。
关于ORGLEVEL，它从1开始（在那里硬编码）。当它在递归集上进一步扩展时，它会获取先前的集合（在第一次迭代时是锚点），并添加1，因为它的表达式是B.[ORGLEVEL] + 1，其中B是前一个集合。这意味着它从1开始（最高领导），并且对于每个后代都会添加1，从而表示组织的所有级别。
当你发现一个员工在ORGLEVEL = 3时，这意味着他有2个经理在他身上。

带有工作示例的逐步说明

让我们跟随这个例子：
EmployeeID  ManagerID
1           NULL
2           1
3           1
4           2
5           2
6           1
7           6
8           6
9           NULL
10          3
11          3
12          10
13          9
14          9
15          13

  1. Anchor: Employees without managers (ManagerID IS NULL). This will start with all the top badass of your company. It's crucial to note that if the anchor set is empty, then the whole recursive CTE will be empty, as there is no starting point and no recursive set to join to.

    SELECT
    EmployeeID = E.EmployeeID,
    ManagerID = NULL, -- Always null by WHERE filter
    HierarchyLevel = 1,
    HierarchyRoute = CONVERT(VARCHAR(MAX), E.EmployeeID)
FROM
    Employee AS E
WHERE
    E.ManagerID IS NULL

这些是哪些：

EmployeeID  ManagerID   HierarchyLevel  HierarchyRoute
1           (null)      1               1
9           (null)      1               9

  1. Recursion N°1: Using this UNION ALL recursion:

    UNION ALL

SELECT
    EmployeeID = E.EmployeeID,
    ManagerID = E.ManagerID,
    HierarchyLevel = R.HierarchyLevel + 1,
    HierarchyRoute = R.HierarchyRoute + ' -> ' + CONVERT(VARCHAR(10), E.EmployeeID)
FROM
    RecursiveCTE AS R
    INNER JOIN Employee AS E ON R.EmployeeID = E.ManagerID

对于这个INNER JOIN，RecursiveCTE有2行（锚定集），其中员工ID为1和9。因此，这个JOIN实际上会返回这个结果。

HierarchyLevel  EmployeeID  ManagerID   HierarchyRoute
2               2           1           1 -> 2
2               3           1           1 -> 3
2               6           1           1 -> 6
2               13          9           9 -> 13
2               14          9           9 -> 14

注意到HierarchyRoute从1和9开始并向每个子代移动吗？我们还将HierarchyLevel增加了1。

由于结果通过UNION ALL关联，所以此时我们有以下结果（步骤1 + 2）：

HierarchyLevel  EmployeeID  ManagerID   HierarchyRoute
1               1           (null)      1
1               9           (null)      9
2               2           1           1 -> 2
2               3           1           1 -> 3
2               6           1           1 -> 6
2               13          9           9 -> 13
2               14          9           9 -> 14

这里是棘手的部分，对于以下迭代中的每个迭代，递归引用RecursiveCTE将仅包含最后一个迭代结果集，而不是累积集合。这意味着对于下一次迭代，RecursiveCTE将表示这些行：

HierarchyLevel  EmployeeID  ManagerID   HierarchyRoute
2               2           1           1 -> 2
2               3           1           1 -> 3
2               6           1           1 -> 6
2               13          9           9 -> 13
2               14          9           9 -> 14

  1. Recursion N°2: Following the same recursive expression...

    UNION ALL

SELECT
    EmployeeID = E.EmployeeID,
    ManagerID = E.ManagerID,
    HierarchyLevel = R.HierarchyLevel + 1,
    HierarchyRoute = R.HierarchyRoute + ' -> ' + CONVERT(VARCHAR(10), E.EmployeeID)
FROM
    RecursiveCTE AS R
    INNER JOIN Employee AS E ON R.EmployeeID = E.ManagerID

考虑到在这个步骤中，RecursiveCTE 只包含具有 HierarchyLevel = 2 的行，那么这个 JOIN 的结果是以下内容（第三级！）：

HierarchyLevel  EmployeeID  ManagerID   HierarchyRoute
3               4           2           1 -> 2 -> 4
3               5           2           1 -> 2 -> 5
3               7           6           1 -> 6 -> 7
3               8           6           1 -> 6 -> 8
3               10          3           1 -> 3 -> 10
3               11          3           1 -> 3 -> 11
3               15          13          9 -> 13 -> 15

这个集合（仅限此！）将在以下递归步骤中作为 RecursiveCTE 使用，并添加到累积的总数中，现在的总数为：

HierarchyLevel  EmployeeID  ManagerID   HierarchyRoute
1               1           (null)      1
1               9           (null)      9
2               2           1           1 -> 2
2               3           1           1 -> 3
2               6           1           1 -> 6
2               13          9           9 -> 13
2               14          9           9 -> 14
3               4           2           1 -> 2 -> 4
3               5           2           1 -> 2 -> 5
3               7           6           1 -> 6 -> 7
3               8           6           1 -> 6 -> 8
3               10          3           1 -> 3 -> 10
3               11          3           1 -> 3 -> 11
3               15          13          9 -> 13 -> 15

  1. Recursion N°3: Starting with level 3s in our working set, the result of the join is:

    HierarchyLevel  EmployeeID  ManagerID   HierarchyRoute
4               12          10          1 -> 3 -> 10 -> 12

这将成为下一递归步骤的工作集。

  1. 第4次递归：从上一步仅有的第4行级别开始，连接的结果没有返回任何行（没有员工的EmployeeID是12且ManagerID也是12）。返回零行标记着迭代的结束。

最终结果集如下：

HierarchyLevel  EmployeeID  ManagerID   HierarchyRoute
1               1           (null)      1
1               9           (null)      9
2               2           1           1 -> 2
2               3           1           1 -> 3
2               6           1           1 -> 6
2               13          9           9 -> 13
2               14          9           9 -> 14
3               4           2           1 -> 2 -> 4
3               5           2           1 -> 2 -> 5
3               7           6           1 -> 6 -> 7
3               8           6           1 -> 6 -> 8
3               10          3           1 -> 3 -> 10
3               11          3           1 -> 3 -> 11
3               15          13          9 -> 13 -> 15
4               12          10          1 -> 3 -> 10 -> 12

以下是完整的示例代码：

CREATE TABLE Employee (EmployeeID INT, ManagerID INT)

INSERT INTO Employee (EmployeeID, ManagerID)
VALUES
  (1, NULL),
  (2, 1),
  (3, 1),
  (4, 2),
  (5, 2),
  (6, 1),
  (7, 6),
  (8, 6),
  (9, NULL),
  (10, 3),
  (11, 3),
  (12, 10),
  (13, 9),
  (14, 9),
  (15, 13)

;WITH RecursiveCTE AS
(
    SELECT
        EmployeeID = E.EmployeeID,
        ManagerID = NULL, -- Always null by WHERE filter
        HierarchyLevel = 1,
        HierarchyRoute = CONVERT(VARCHAR(MAX), E.EmployeeID)
    FROM
        Employee AS E
    WHERE
        E.ManagerID IS NULL

    UNION ALL

    SELECT
        EmployeeID = E.EmployeeID,
        ManagerID = E.ManagerID,
        HierarchyLevel = R.HierarchyLevel + 1,
        HierarchyRoute = R.HierarchyRoute + ' -> ' + CONVERT(VARCHAR(10), E.EmployeeID)
    FROM
        RecursiveCTE AS R
        INNER JOIN Employee AS E ON R.EmployeeID = E.ManagerID
)
SELECT
    R.HierarchyLevel,
    R.EmployeeID,
    R.ManagerID,
    R.HierarchyRoute
FROM
    RecursiveCTE AS R
ORDER BY
    R.HierarchyLevel,
    R.EmployeeID
我理解你的解释，但我想了解“B.[ORGLEVEL] + 1”这部分。我不明白它如何定义组织级别以及如何迭代。为什么它不是在第二个员工之后立即迭代？ - Vijay Thapa
1
@VijayThapa 我在结尾处添加了一条有关orglevel的评论。 - EzLo
但是它如何区分这些级别？我需要解释一下。 - Vijay Thapa
2
非常好，感谢您的时间。 - MemeDeveloper
2
非常棒！非常感谢您的时间。 - undefined
显示剩余2条评论
1
如果您有多个高级经理，则[ORGLEVEL]始终从1开始。
没有发布数据，无法提供详细信息。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接