SQL Server CTE 递归查询全解

CTE（Common Table Expression，通用表达式）是 SQL Server 中的一种临时结果集，可以像视图一样使用。递归查询是一种特殊的查询方式，可以处理具有层次结构的数据。CTE 递归查询可以通过 WITH 子句实现。

下面是一个例子，查询某个员工的所有下属：

WITH EmployeeHierarchy AS
(
    SELECT EmployeeID, SupervisorID, FirstName, LastName, 1 AS Level
    FROM Employees
    WHERE EmployeeID = @EmployeeID
    UNION ALL
    SELECT e.EmployeeID, e.SupervisorID, e.FirstName, e.LastName, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.SupervisorID = eh.EmployeeID
)
SELECT EmployeeID, SupervisorID, FirstName, LastName, Level
FROM EmployeeHierarchy
ORDER BY Level, LastName, FirstName

这个查询中，EmployeeHierarchy 是一个递归 CTE，它的初始查询返回指定员工的基本信息和层级（Level）为 1。然后，递归查询连续地将下属的信息加入到结果集中，直到没有下属为止。

注意，递归查询必须包含一个初始查询和一个递归查询，它们之间使用 UNION ALL 连接。初始查询返回基本信息，递归查询使用先前的结果集来获得更多信息。递归查询必须引用 CTE 的名称，以便递归调用它本身。在递归查询中，必须有一个递归终止条件，否则查询将进入无限循环。在上面的例子中，递归终止条件是没有下属。

最后，查询结果按照层级、姓氏和名字排序，以显示员工的层次结构。