sql-server - 在SQL Server中，如何选择递归表中的所有记录？

Question

我有一个递归表，其中每条记录都有一个 ID 和一个 PARENTID。 PARENTID 指向同一个表中的不同 ID。 SQL Server 中有没有一种方法可以在一个语句中选择整个“树”？我可以编写一个递归函数来从父级跳转到所有子级，但我想要一种在一个查询中完成它的方法。

在 Oracle 中，它看起来像这样:

select
  id,
  parentid,
  nodename
from 
  MY_SCHEMA.MY_TABLE
  connect by nocycle prior parentid = id
start with id = :starting_id_number
order by
  id 

SQL Server 的等价物是什么？

Answer 1

这是我为您整理的示例。它演示了使用递归公用表表达式 (CTE)。

CREATE TABLE #tempTable
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    parentID INT NULL
)

INSERT INTO #tempTable (parentID) VALUES (null)
INSERT INTO #tempTable (parentID) VALUES (1)
INSERT INTO #tempTable (parentID) VALUES (1)
INSERT INTO #tempTable (parentID) VALUES (2)
INSERT INTO #tempTable (parentID) VALUES (3)
INSERT INTO #tempTable (parentID) VALUES (2)
INSERT INTO #tempTable (parentID) VALUES (5)


SELECT * FROM #tempTable;

WITH RecursiveTable (ID, ParentID, Level)
AS
(
    --Anchor
    SELECT  tt.ID, 
            tt.ParentID, 
            0 AS Level
    FROM #tempTable AS tt
    WHERE parentID IS null
    UNION ALL
    --Recursive member definition
    SELECT  tt.ID, 
            tt.ParentID, 
            LEVEL + 1
    FROM #tempTable AS tt
        INNER JOIN RecursiveTable rt ON
        tt.ParentID = rt.ID
)
SELECT * 
FROM RecursiveTable

DROP TABLE #tempTable

编辑:作为一个额外的想法，在 SQL Server 2008 中有一种称为 hierarchyid 的数据类型，可用于实现分层数据结构。看下面的教程

http://technet.microsoft.com/en-us/library/bb677213.aspx