SQL Server-以多对多的关系获取行中的所有子级? [英] SQL Server - Get all children of a row in many-to-many relationship?
问题描述
我正在尝试在SQL Server中编写一个递归查询,该查询基本上列出了给定父级的父子级层次结构.父母可以有多个孩子,而一个孩子可以属于多个父母,因此它以多对多关系存储.
I'm trying to write a recursive query in SQL Server that basically lists a parent-child hierarchy from a given parent. A parent can have multiple children and a child can belong to multiple parents so it is stored in a many-to-many relation.
我从另一个有点相关的问题中修改了以下查询,但这并没有完全解决问题,仅选择了第一级子级...
I modified the following query from another somewhat related question, however this doesn't go all the way up to the tree and only selects the first level child...
DECLARE @ObjectId uniqueidentifier
SET @ObjectId = '1A213431-F83D-49E3-B5E2-42AA6EB419F1';
WITH Tree AS
(
SELECT A.*
FROM Objects_In_Objects A
WHERE A.ParentObjectId = @ObjectId
UNION ALL
SELECT B.*
FROM Tree A
JOIN Objects_In_Objects B
ON A.ParentObjectId = B.ObjectId
)
SELECT *
FROM Tree
INNER JOIN Objects ar on tree.ObjectId = ar.ObjectId
有人知道如何修改查询,使其一直沿树"走下去吗?还是使用上述结构无法做到这一点?
Does anyone know how to modify the query to go all the way down the 'tree'? Or is this not possible using the above construction?
对象
列:ObjectId
| Name
Objects_In_Objects
列:ObjectId
| ParentObjectId
样本数据:
对象
ObjectId | Name
1A213431-F83D-49E3-B5E2-42AA6EB419F1 | Main container
63BD908B-54B7-4D62-BE13-B888277B7365 | Sub container
71526E15-F713-4F03-B707-3F5529D6B25E | Sub container 2
ADA9A487-7256-46AD-8574-0CE9475315E4 | Object in multiple containers
对象中的对象
ObjectId | ParentObjectId
ADA9A487-7256-46AD-8574-0CE9475315E4 | 71526E15-F713-4F03-B707-3F5529D6B25E
ADA9A487-7256-46AD-8574-0CE9475315E4 | 63BD908B-54B7-4D62-BE13-B888277B7365
63BD908B-54B7-4D62-BE13-B888277B7365 | 1A213431-F83D-49E3-B5E2-42AA6EB419F1
71526E15-F713-4F03-B707-3F5529D6B25E | 1A213431-F83D-49E3-B5E2-42AA6EB419F1
推荐答案
这样的递归CTE(公用表表达式)将一直沿用 .
Such a recursive CTE (Common Table Expression) will goo all the way .
尝试一下:
;WITH Tree AS
(
SELECT A.ObjectID, A.ObjectName, o.ParentObjectID, 1 AS 'Level'
FROM dbo.Objects A
INNER JOIN dbo.Objects_In_Objects o ON A.ObjectID = o.ParentObjectID
WHERE A.ObjectId = @ObjectId -- use the A.ObjectId here
UNION ALL
SELECT A2.ObjectID, A2.ObjectName, B.ParentObjectID, t.Level + 1 AS 'Level'
FROM Tree t
INNER JOIN dbo.Objects_In_Objects B ON B.ParentObjectID = t.ObjectID
INNER JOIN dbo.Objects A2 ON A2.ObjectId = B.ObjectId
)
SELECT *
FROM Tree
INNER JOIN dbo.Objects ar on tree.ObjectId = ar.ObjectId
如果您更改此设置-这项功能现在对您有用吗? (我添加了Level
列-通常有助于理解层次结构中每一行的深度")
If you change this - does this work for you now? (I added a Level
column - typically helps to understand the "depth" in the hierarchy for every row)
我似乎确实在我的SQL Server实例上获得了正确的输出,至少...
I do seem to get the proper output on my SQL Server instance, at least...
这篇关于SQL Server-以多对多的关系获取行中的所有子级?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!