SQL Server中的ORACLE Connect by子句等效 [英] ORACLE Connect by clause equivalent in SQL Server
问题描述
在SQL Server中是否有与Oracle的CONNECT BY等效的子句.使用parentId字段构建类别树的要求.
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 2005+等效的Oracle CONNECT BY
层次查询语法是使用递归CTE. SQL Server 2008添加了 HierarchyID .这是递归CTE的示例:
The SQL Server 2005+ equivalent of Oracle's CONNECT BY
hierarchical query syntax is to use a recursive CTE. SQL Server 2008 added HierarchyID. Here's an example of a recursive 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 中获取了示例查询.
Googling "hierarchical CTE" and/or "recursive CTE" will turn up numerous results. I took the example query from the 4GuysFromRolla.com.
递归CTE现在是ANSI标准-据我所知,直到Oracle 11g才支持该语法.
Recursive CTEs are now ANSI standard - the syntax wasn't supported until Oracle 11g as I understand.
这篇关于SQL Server中的ORACLE Connect by子句等效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!