与SQL Server中的Oracle Connect By子句等效

SQL Server 2005+相当于Oracle的 CONNECT BY 层次查询语法是使用递归CTE。添加了SQL Server 2008 HierarchyID .以下是递归CTE的示例:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS (
   SELECT EmployeeID,
          LastName,
          FirstName,
          ReportsTo,
          1 as HierarchyLevel
     FROM Employees
    WHERE ReportsTo IS NULL
   UNION ALL
   -- Recursive step
   SELECT e.EmployeeID,
          e.LastName,
          e.FirstName,
          e.ReportsTo,
          eh.HierarchyLevel + 1 AS HierarchyLevel
     FROM Employees e
     JOIN EmployeeHierarchy eh ON e.ReportsTo = eh.EmployeeID)
  SELECT *
    FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName 

谷歌搜索“分层CTE”和/或“递归CTE”将得到许多结果。我从 4GuysFromRolla.com .

递归CTE现在是ANSI标准——据我所知,直到Oracle11g才支持该语法。

有 HierarchyID MS SQL Server 2008中的数据类型,可以使您的生活更轻松。