在 SQL Server 中等效的 ORACLE Connect by 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2200636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE Connect by clause equivalent in SQL Server
提问by zapping
Is there a equivalent clause to CONNECT BY of Oracle in SQL Server. The requirement to build a category tree using a parentId field.
SQL Server 中是否有与 Oracle 的 CONNECT BY 等效的子句。使用 parentId 字段构建类别树的要求。
回答by OMG Ponies
The SQL Server 2005+ equivalent of Oracle's CONNECT BYhierarchical query syntax is to use a recursive CTE. SQL Server 2008 added HierarchyID. Here's an example of a recursive CTE:
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
Googling "hierarchical CTE" and/or "recursive CTE" will turn up numerous results. I took the example query from the 4GuysFromRolla.com.
谷歌搜索“分层 CTE”和/或“递归 CTE”会得到很多结果。我从4GuysFromRolla.com 获取了示例查询。
Recursive CTEs are now ANSI standard - the syntax wasn't supported until Oracle 11g as I understand.
递归 CTE 现在是 ANSI 标准 - 据我所知，直到 Oracle 11g 才支持该语法。
回答by Ivan Krechetov
There's HierarchyIDdata type in MS SQL Server 2008, which can make your life easier.
MS SQL Server 2008 中有HierarchyID数据类型，可以让您的生活更轻松。
