如何在SQL Server中递归扩展行？

发布于 2025-02-02 20:52:13 字数 766 浏览 1 评论 0原文

在MS SQL Server中，如果我在会员表中有2个表格

Group
------------
ID GroupName
1  A
2  B
3  C

GroupMembership
---------------
Value GroupID  Type
1     2        Node
3     2        Node
2     1        Group
4     1        Node
5     3        Node

，如果类型为节点，则只是一个正常值。但是，如果其类型是组，则它指的是在值为groupID的组表中定义的组。

因此，以上对GroupID = 1的查询将导致这种树，

4, (1, 3)

但是从理论上讲，我可以通过正确设置两个表之间的组引用来制作任何类型的树，并具有多种级别的嵌套括号。

问题是，我该如何为groupID = 1进行选择，以便将所有相关记录（甚至是深嵌套的记录）递归扩展并返回成员资格记录？

因此，理想情况下，它将返回

1     2        Node
3     2        Node
2     1        Group
4     1        Node

我当前的查询只会返回此问题，因为它不知道如何递归扩展行。

2     1        Group
4     1        Node

什么是简单的SQL查询？

原文

In MS SQL Server, if I have 2 tables like so

Group
------------
ID GroupName
1  A
2  B
3  C

GroupMembership
---------------
Value GroupID  Type
1     2        Node
3     2        Node
2     1        Group
4     1        Node
5     3        Node

In the membership table, if the type is Node, then it is just a normal value. But if its type is Group, then it is referring to the group defined in the Group table where the Value is the GroupID.

So the above querying for GroupID=1, would result in this kind of tree

4, (1, 3)

But in theory I could make any kind of tree with any amount of levels of nested brackets, by correctly setting up the group references between both tables.

The question is, how can I do a select statement for GroupID=1, such that it would recursively expand all the related records (even deeply nested ones) and return the membership records?

So ideally it would return

1     2        Node
3     2        Node
2     1        Group
4     1        Node

My current query only returns this as it doesn't know how to expand the rows recursively.

2     1        Group
4     1        Node

What is a simple sql query to do this?

分享到QQ

分享到微博

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

发布评论

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

￡噩梦荏苒 2025-02-09 20:52:13

您应该使用带有递归的公共表表达式：

DECLARE @GroupMembership TABLE
(
    Value   INT,
    GroupID INT,
    Type    VARCHAR(5)
);

INSERT INTO @GroupMembership (Value, GroupID, Type   )
                      VALUES (1    , 2      , 'Node' ),
                             (3    , 2      , 'Node' ),
                             (2    , 1      , 'Group'),
                             (4    , 1      , 'Node' ),
                             (5    , 3      , 'Node' );


DECLARE @requestingGroupID INT = 1;

WITH Recursion AS (
    SELECT Value, GroupID, Type
    FROM @GroupMembership
    WHERE GroupID = @requestingGroupID
    UNION ALL
    SELECT M.Value, M.GroupID, M.Type
    FROM Recursion AS R
    INNER JOIN @GroupMembership AS M ON R.Value = M.GroupID AND R.Type = 'Group'
)
SELECT *
FROM Recursion

结果：resuly：

value	grout groupid	type
2	1	group
4 group 4	1	节点
1	2	节点
3	2	节点

You should use Common table expression with recursion:

DECLARE @GroupMembership TABLE
(
    Value   INT,
    GroupID INT,
    Type    VARCHAR(5)
);

INSERT INTO @GroupMembership (Value, GroupID, Type   )
                      VALUES (1    , 2      , 'Node' ),
                             (3    , 2      , 'Node' ),
                             (2    , 1      , 'Group'),
                             (4    , 1      , 'Node' ),
                             (5    , 3      , 'Node' );


DECLARE @requestingGroupID INT = 1;

WITH Recursion AS (
    SELECT Value, GroupID, Type
    FROM @GroupMembership
    WHERE GroupID = @requestingGroupID
    UNION ALL
    SELECT M.Value, M.GroupID, M.Type
    FROM Recursion AS R
    INNER JOIN @GroupMembership AS M ON R.Value = M.GroupID AND R.Type = 'Group'
)
SELECT *
FROM Recursion

Result: