层次查询与公用表表达式(Cte)的区别

Question

在一次面试中，面试官问什么是分层查询，我回答了CTE (Common table expression )，但他说两者是不同的，有什么不同？

Answer 1

正如Magnus指出的，CTE不需要用于分层查询。它可以用在我们需要临时存储一些数据的任何地方，我们想要在这些数据上执行一些操作，比如应用RANK()函数。例如，考虑查询在一系列值中查找最大的nth。我们可以通过使用CTE简单而有效地实现这一点。

Eg(无递归)：

     WITH CTE AS
        (SELECT *,
        RANK() OVER(ORDER BY Amount DESC) AS GrantRank
        FROM [Grant] )
        SELECT * FROM CTE
        WHERE GrantRank = 3

当然，我们可以使用CTE来使用递归实现分层查询。

Eg(递归调用)：

WITH CTE_EMPLOYEE_HEIRARCHY AS
(
    SELECT E.EmployeeID, E.ReportsTo AS Supervisor, E.FirstName, E.LastName
    FROM EMPLOYEES E WHERE E.EmployeeID = 5

    UNION ALL

    SELECT E1.EmployeeID, E1.ReportsTo AS Supervisor, E1.FirstName, E1.LastName
    FROM CTE_EMPLOYEE_HEIRARCHY 
    JOIN EMPLOYEES E1
    ON E1.ReportsTo = CTE_EMPLOYEE_HEIRARCHY.EmployeeID 

)

SELECT * FROM CTE_EMPLOYEE_HEIRARCHY

参考： 1:SQLAuthority Blog 2:Code Project Article