sql - 如何在SQL中递归地 self 加入？

Question

我有一张 table :

Series========IDSeriesNameParentSeriesID

A series can be a "root" series, (ParentSeriesID is 0 or null) or it can have a Parent. A series can also be several levels down, i.e. its Parent has a Parent, which has a Parent, etc.

How can I query the table to get a Series by it's ID and ALL descendant Series' ?

So far I have tried:

 SELECT child.*
 FROM Series parent JOIN Series child ON child.ParentSeriesID = parent.ID
 WHERE parent.ID = @ParentID

但这仅返回第一级子节点，我想要父节点和所有“下游”节点。我不确定如何从这里取得进展。

Answer 1

如果您使用的是 SQL Server 2005+，则可以使用公用表表达式

With Family As 
( 
Select s.ID, s.ParentSeriesId, 0 as Depth
From Series s
Where ID = @ParentID 
Union All 
Select s2.ID, s2.ParentSeriesId, Depth + 1
From Series s2
    Join Family 
        On Family.ID = s2.ParentSeriesId 
) 
Select *
From Family 

更多信息:

Recursive Queries Using Common Table Expressions