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

I have a recursive table in which each record has an ID and a PARENTID. PARENTID points to a different ID in the same table. Is there a way in SQL Server to select an entire "tree" in one statement? I could write a recursive function to jump from a parent to all the children, but I'd like a way to do it in one query.

我有一个递归表,其中每个记录都有一个ID和PARENTID。 PARENTID指向同一个表中的不同ID。 SQL Server中是否有一种方法可以在一个语句中选择整个“树”?我可以编写一个递归函数来从父级跳转到所有子级,但我想在一个查询中执行此操作。

In Oracle, it would look like this:

在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

What would the SQL Server equivalent be?

SQL Server等价物是什么?

6 个解决方案

#1

Here is an example I have thrown together for you. It demonstrates using a recursive common table expression (CTE).

这是我为你抛出的一个例子。它演示了使用递归公用表表达式(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

Edit: As an additional thought, in SQL Server 2008 there is a data type called hierarchyid that can be used to implement hierarchical data structures. See the following tutorial

编辑:作为一个额外的想法,在SQL Server 2008中有一个名为hierarchyid的数据类型,可用于实现分层数据结构。请参阅以下教程

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

#2

You can use CTE's like this;

你可以像这样使用CTE;

CREATE TABLE TestTable
( 
    ID int primary key NOT NULL,
    ParentID int
)

INSERT INTO TestTable VALUES (0, null)
INSERT INTO TestTable VALUES (1, 0)
INSERT INTO TestTable VALUES (2, 0)
INSERT INTO TestTable VALUES (3, 1)
INSERT INTO TestTable VALUES (4, 3)


-- Get branch
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
            Child.ID,
            Child.ParentID,
            Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
        FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE WHERE IDPath LIKE '%.1.%' ORDER BY ParentID ASC 


-- Get complete tree:
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
            Child.ID,
            Child.ParentID,
            Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
        FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE ORDER BY ParentID ASC

#3

id also had this problem but i didn't find a solution. so i created this workaround: i added a new column 'tree_id' so that my table looks like this.

id也有这个问题,但我没有找到解决方案。所以我创建了这个解决方法:我添加了一个新列'tree_id',以便我的表看起来像这样。

treeid|id|parentid|nodename
1     |1 | null   | rootOfTreeOne
1     |2 | 1      | childOfRootOne
1     |3 | 1      | secondChild
2     |4 | null   | rootOfSecondTree
2     |5 | 4      | childofSecondTree

and created the tree in a program. the advantage is that you can get all nodes from the tree with one simple single select statement.

并在程序中创建了树。优点是您可以使用一个简单的单个select语句从树中获取所有节点。

select * from tree_table where tree_id = 1;

but there also maybe some stored procedures that can help you solve your problem.

但也可能有一些存储过程可以帮助您解决问题。

hope this helps..

希望这可以帮助..

#4

I don't think you can get away with it without using recursion in your statement.

我不认为你可以在你的陈述中不使用递归而逃脱它。

If you are able (or for future reference) you could try using modified preorder tree traversal, which will allow you to do this.

如果您能够(或供将来参考),您可以尝试使用修改后的预订树遍历,这将允许您执行此操作。

Explaining modified preorder tree traversal in this answer is out of the scope because it takes some explaining and playing to get to grips with.

在这个答案中解释修改后的预订树遍历超出了范围,因为它需要一些解释和播放才能掌握。

I will point out that there is an overhead on MPTT for updating and inserting records, but selecting is generally significantly more efficient. In general, selects happen a whole lot more than updates/inserts so it's worth it, but it's worth having a think about your particular situation before diving straight in.

我将指出MPTT有更新和插入记录的开销,但选择通常效率更高。一般来说,选择比更新/插入更多,所以它是值得的,但是在直接进入潜水之前考虑一下你的特殊情况是值得的。

The article I've linked to I find an excellent explanation of MPTT.

我与之相关的文章找到了对MPTT的一个很好的解释。

#5

You should be able to use Common Table Expressions to do a recursive query. Do a google search on "Recursive Queries Using Common Table Expressions", there is a msdn article.

您应该能够使用公用表表达式来执行递归查询。在谷歌搜索“使用公用表表达式的递归查询”,有一篇msdn文章。

sigh new users aren't allowed to add hyperlinks.

叹息新用户不允许添加超链接。

#6

另请参阅SQL Server问题中的其他树导航

#1