我有一张桌子：
Series ======== ID SeriesName ParentSeriesID
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
但这仅返回子级的第一层，我需要父节点，以及所有“下游”节点。我不确定如何从这里开始。
最佳答案
如果您使用的是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