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

Question

谁能帮助我了解此递归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;

Answer 1

SQL Server中的递归CTE有2部分:

anchor :是您递归的起点。此集合将通过递归联接进一步扩展。

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

看来，它正在获取所有没有经理的员工(可能是最高老板，也可能是树型关系的根源)。

递归:与 UNION ALL链接，此集合必须引用声明CTE(因此使其递归)。可以将其视为如何扩展 anchor 定结果的下一个层次。

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

在此示例中，我们正在获取(第一次迭代) anchor 定结果集(所有没有经理的雇员)，并通过 RECURSIVETBL将它们与 MANAGERID联接在一起，因此 A.EMPID将保留先前选择的经理的雇员。只要每个最后的结果集都可以生成新行，这种连接就会不断进行。

可以在递归部分上放置的内容有一些限制(例如，没有分组或其他嵌套的递归)。另外，因为它以 UNION ALL开头，所以它的规则也适用(列的数量和数据类型必须匹配)。

关于 ORGLEVEL ，它以设置为1的 anchor 开始(在此处进行硬编码)。当在递归集上进一步扩展时，它将获取上一个集( anchor ，在第一次迭代中)并加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

anchor :没有经理的员工(ManagerID IS NULL)。这将从公司的所有头号坏蛋开始。至关重要的是要注意，如果 anchor 集为空，则整个递归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

递归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行( anchor 集)，其员工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

递归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

递归N°3 :从工作集中的3s级别开始，联接的结果为:

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

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

递归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

这是完整的 fiddle和代码:

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