如何在 SQL 中递归地自我加入? [英] How to self JOIN recursively in SQL?
问题描述
我有一张桌子:
<前>系列========ID系列名称父系列ID一个系列可以是一个根"系列,(ParentSeriesID
是 0 或 null)或者它可以有一个父级.一个系列也可以向下几个级别,即它的 Parent 有一个 Parent,它有一个 Parent 等等.
如何查询表以通过其 ID 和所有后代系列来获取系列?
到目前为止我已经尝试过:
SELECT child.*FROM 系列父 JOIN 系列子 ON child.ParentSeriesID = parent.ID哪里 parent.ID = @ParentID
但这仅返回第一级子节点,我想要父节点,以及所有下游"节点.我不确定如何从这里取得进展.
如果您使用的是 SQL Server 2005+,则可以使用公用表表达式
和家人一样(选择 s.ID, s.ParentSeriesId, 0 作为深度从系列 s其中 ID = @ParentID联合所有选择 s2.ID、s2.ParentSeriesId、深度 + 1从系列 s2加入家庭在 Family.ID = s2.ParentSeriesId)选择 *来自家人
更多:
I have a table:
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
But this only returns the first level of children, I want the parent node, and all "downstream" nodes. I am not sure how to progress from here.
If you are using SQL Server 2005+, you can use common-table expressions
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
For more:
Recursive Queries Using Common Table Expressions
这篇关于如何在 SQL 中递归地自我加入?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!