如何在SQL Server中进行上递归自连接?

How can I do a recursive self-join in SQL Server ? I have a table like this:

如何在SQL Server中进行递归自连接?我有一张这样的桌子:

TableID | ParentID
   1    |     NULL
   2    |        1
   3    |        1
   4    |        3
   5    |     NULL
   6    |        4
   7    |        6

I want to get the following results based on given TableID to get all the ParentsID related to the TableID, let's say I want to get all the parents for the TableID = 6 :

我想根据给定的TableID得到如下的结果，得到与TableID相关的所有父sid，假设我想让这个TableID = 6的所有父sid:

TableID
   6
   4
   3
   1

I'm stuck on this and i don't know how to get the result in SQL Query ... Hope to tell me the SQL Query to get the previous data

我被困在这里了，我不知道如何得到SQL查询的结果…希望能告诉我SQL查询以获取之前的数据。

1 个解决方案

#1

It should be

它应该是

; WITH MyQuery (TableID, ParentID, Level) AS
(
    SELECT M.TableID, M.ParentID, 0 AS Level 
        FROM MyTable M 
        WHERE M.TableID = 6 -- Here it's the row number where the query starts

    UNION ALL

    SELECT M.TableID, M.ParentID, Q.Level + 1 
        FROM MyTable M 
        INNER JOIN MyQuery Q ON M.TableID = Q.ParentID
)

SELECT * FROM MyQuery;

and as written by Byers, it's a Recursive Queries Using Common Table Expressions

正如Byers所写，这是一个使用公共表表达式的递归查询。

The Level column is useless (it isn't "useless useless". It's useless for what you asked), I have added it because it's quite often inserted in these recursive queries. If you don't need it, delete it from the 3 places it appears.

Level列是无用的(它不是“无用的无用的”)。它对您所要求的没有用处)，我添加了它，因为它经常插入到这些递归查询中。如果你不需要它，从它出现的3个地方删除它。

It seems to be much more complex to do the Level in reverse (so that the grand-grand father is level 0, his childs are level 1...)

反过来做这个级别似乎要复杂得多(所以祖父级的父亲是0级，他的孩子是1级…)

Note that this code will work with SQL Server >= 2005

注意，该代码将与SQL Server >= 2005一起工作

#1