递归查询CTE

Question

我正在看“Murach‘sSQLServer2016forDevelopers”一书中的一个例子。该示例说明了如何在SQL中编写递归CTS。我非常了解递归函数(在C#中)，但是我不知道SQL递归逻辑是如何工作的。下面是一个例子：

USE Examples;

WITH EmployeesCTE AS
(
        -- Anchor member
        SELECT EmployeeID, 
            FirstName + ' ' + LastName As EmployeeName, 
            1 As Rank
        FROM Employees
        WHERE ManagerID IS NULL
    UNION ALL
        -- Recursive member
        SELECT Employees.EmployeeID, 
            FirstName + ' ' + LastName, 
            Rank + 1
        FROM Employees
            JOIN EmployeesCTE
            ON Employees.ManagerID = EmployeesCTE.EmployeeID
)
SELECT *
FROM EmployeesCTE
ORDER BY Rank, EmployeeID;

此查询返回组织中员工的层次结构级别。

我的问题是:在递归函数中，您将看到一个终止递归的递减变量(通过到达一个基本情况)。我的问题是:在EmployeesCTE中对应的部分在哪里？请帮我理解一下逻辑。

Answer 1

因此，我们所称的“递归CTE”实际上应该称为迭代CTE。其思想是，为了定义递归表(在本例中为EmployeesCTE)，我们首先创建一些初始行，在本例中，这是通过

   SELECT EmployeeID, 
        FirstName + ' ' + LastName As EmployeeName, 
        1 As Rank
    FROM Employees
    WHERE ManagerID IS NULL

(注意，这不包含对EmployeesCTE的引用，因此它不是递归的)，然后我们迭代一个表达式，在本例中

    SELECT Employees.EmployeeID, 
        FirstName + ' ' + LastName, 
        Rank + 1
    FROM Employees
        JOIN EmployeesCTE
        ON Employees.ManagerID = EmployeesCTE.EmployeeID

来生成更多的行。我们一直这样做，直到该表达式不返回任何行。在这个表达式中，EmployeesCTE指的是该表的前一个版本，通过计算它，我们计算出该表的下一个版本。

因此，停止递归(或者更确切地说是迭代)的条件是，递归表达式不产生新的行。

现在，让我们仔细看看上述所有内容是如何应用到您给出的特定示例中的。我们最初的一组行由没有经理的员工组成(我们称他们为1级员工)。然后，我们找到所有在前一步中由员工管理的员工(我们称他们为2级员工)。然后，我们发现员工是由2级员工管理的，并将其称为3级，以此类推。最终，我们将到达一个无法找到新员工的步骤(当然，假设由关系管理的员工没有周期)。

Answer 2

由于您熟悉C#，您可能会认为这是一个复杂的对象modell。

想象一下一个简单的Windows.Forms.Form及其控件。每个控件都有一个控件--集合本身。在数据库中，您可以想到一个自引用表，其中每一行指向其父行(顶层对象指向空)，就像员工指向层次结构上的下一个老板一样。

有一个带有Refresh()方法的top对象。调用它时，函数对自己的内容执行一些操作，并对其内部集合调用Refresh()。集合对其所有成员调用Refresh()。他们都做了一些事情，并在他们的内部集合上调用Refresh()。这在嵌套模型上运行，直到您到达带有空Controly集合的控件为止。

这更像是一个自上而下的级联。实际上，故意使用条件来停止递归CTE是非常棘手的，因为您不会得到带有中断条件的最后一行。

当JOIN操作不返回任何行时，递归CTE的第二部分自然结束。

在您的例子中，您可以将此理解为

• 锚:找所有没有老板的员工(最高级别)。
• 现在询问所有有一名员工担任经理的员工名单(二级)。
• 一排排地走下去，把所有有二级员工担任经理的员工找来。
• 继续工作，直到不再依赖员工。

请注意，递归CTE是一种缓慢的方法，因为它是一个隐藏的RBAR。

Answer 3

可能需要做一些自顶向下的MS来将顶级值向下传播到所有级别(在某些情况下，只有TOP才会更好;-)

    SELECT  ID      AS ID
        ,   parent  AS parent
        ,   limit   AS limit
        ,   NULL    AS maxParentLimit -- place holder
        INTO #StructLimit_CTE
        FROM StructLimit_CTE
    CREATE INDEX #StructLimit_CTE_id ON #StructLimit_CTE(ID)
    --SELECT COUNT(*) FROM #StructLimit_CTE -- 1000000+ in my case, so MS needs index  :-)

    ;WITH maxParentLimit (ID, ParentLimit, limit) as 
    ( select ID 
            , limit 
            , limit             
            from #StructLimit_CTE
            where parent IS NULL -- = 0
        union all
            select o.ID                                     
                , IIF( o.limit > ParentLimit, o.limit, ParentLimit) -- take the biggst, when we have one, think about NULL-s
                , o.limit   
            from #StructLimit_CTE o
            join maxParentLimit n on n.ID = o.parent -- recursion
    )
    UPDATE #StructLimit_CTE SET maxParentLimit = m.ParentLimit 
        FROM #StructLimit_CTE   AS o    
        JOIN maxParentLimit AS m    ON o.ID = m.ID
    -- or use (LEFT) JOIN when proper