时间戳

首页 > 解决方案 > 递归 CTE 如何在 SQL Server 中工作？

sql - 递归 CTE 如何在 SQL Server 中工作？

问题描述

谁能帮我理解这个递归 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;

标签： sqlsql-serverrecursioncommon-table-expression

解决方案

SQL Server 中的递归 CTE 有 2 个部分：

Anchor：是递归的起点。这是一个将通过递归连接进一步扩展的集合。

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

似乎它正在获取所有没有任何经理的员工（可能是最高老板，或树关系的根源）。

递归：与 a 链接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

在此示例中，我们正在（在第一次迭代中）获取锚结果集（所有没有经理的员工）并使用RECURSIVETBLthrough加入它们MANAGERID，因此A.EMPID将保留先前选择的经理的员工。只要每个最后一个结果集都可以生成新行，这种连接就会一直持续下去。

您可以在递归部分上放置的内容有一些限制（例如，没有分组或其他嵌套递归）。此外，由于它以 a 开头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. 锚：没有经理的员工（ManagerID IS NULL）。这将从贵公司的所有顶级坏蛋开始。需要注意的是，如果锚集是空的，那么整个递归 CTE 将是空的，因为没有起点，也没有要加入的递归集。

    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. 递归 N°1：使用此UNION ALL递归：

    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 行（锚集），员工 ID1和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。

因为结果由 a 链接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. 递归N°2：遵循相同的递归表达式......

    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 的结果如下（第 3 级！）：

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. 递归 N°3：从我们工作集中的第 3 级开始，连接的结果是：

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

这成为我们下一个递归步骤的工作集。

  1. 递归 N°4：从上一步中唯一的第 4 行开始，连接的结果不产生任何行（没有员工的 EmployeeID 为 12 作为 ManagerID）。不返回任何行标志着迭代的结束。

最终结果集很高：

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

推荐阅读