返回自连接表上的父/子关系

发布于 2024-09-18 02:04:12 字数 304 浏览 1 评论 0原文

我需要能够使用 SQL 返回所有级别的给定父 ID 的所有子列表。

该表看起来像这样：

ID   ParentId   Name
---------------------------------------
1    null       Root
2    1          Child of Root
3    2          Child of Child of Root

给出一个 ID '1'，我将如何返回整个列表......？嵌套深度也没有限制...

谢谢，
基隆

原文

I need to be able to return a list of all children given a parent Id at all levels using SQL.

The table looks something like this:

ID   ParentId   Name
---------------------------------------
1    null       Root
2    1          Child of Root
3    2          Child of Child of Root

Give an Id of '1', how would I return the entire list...? There is no limitation on the depth of the nesting either...

Thanks,
Kieron

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

也只是曾经 2024-09-25 02:04:13

要获取以这种方式存储的给定 @ParentId 的所有子项，您可以使用递归 CTE。

declare @ParentId int
--set @ParentId = 1

;WITH T AS
(
select 1 AS ID,null AS ParentId, 'Root' as [Name] union all
select 2,1,'Child of Root' union all
select 3,2,'Child of Child of Root'
),
cte AS
(
SELECT ID, ParentId, Name
FROM T 
WHERE ParentId = @ParentId  OR (ParentId IS NULL AND @ParentId IS NULL)
UNION ALL
SELECT T.ID, T.ParentId, T.Name
FROM T 
JOIN cte c ON c.ID = T.ParentId
)
SELECT ID, ParentId, Name
FROM cte
OPTION (MAXRECURSION 0)

To get all children for a given @ParentId stored in that manner you could use a recursive CTE.

declare @ParentId int
--set @ParentId = 1

;WITH T AS
(
select 1 AS ID,null AS ParentId, 'Root' as [Name] union all
select 2,1,'Child of Root' union all
select 3,2,'Child of Child of Root'
),
cte AS
(
SELECT ID, ParentId, Name
FROM T 
WHERE ParentId = @ParentId  OR (ParentId IS NULL AND @ParentId IS NULL)
UNION ALL
SELECT T.ID, T.ParentId, T.Name
FROM T 
JOIN cte c ON c.ID = T.ParentId
)
SELECT ID, ParentId, Name
FROM cte
OPTION (MAXRECURSION 0)

回复收藏 0 原文

~没有更多了~