I don't even know how to title this question but I hope this makes sense.

我甚至不知道如何标题这个问题，但我希望这是有道理的。

I have three tables:

我有三张桌子：

A table of users and their details
用户及其详细信息表
A table of groups and its details
组表及其详细信息
A table of groups with the lookup ID of its members which are either from the users or groups table
具有其成员的查找ID的组的表，这些成员来自用户或组表

The 3rd table has two columns:

第3个表有两列：

A group ID which matches a row in the groups (2nd) table
与组（第2个）表中的行匹配的组ID
A member ID which matches a row in either the users (1st) or groups (2nd) table
与用户（第1个）或组（第2个）表中的行匹配的成员标识

Notes:

笔记：

The IDs of users and groups table are unique against each other. For example, if a user has ID = 1 then no other user, nor any group, will have the same ID.
用户和组表的ID彼此唯一。例如，如果用户ID = 1，则其他用户或任何组都不会拥有相同的ID。
A group can have either a user or another group as a member.
组可以具有用户或其他组作为成员。

I need to dump a view of the 3rd table, fully expanding all members of each group, including the nested groups, dump the group path, and handle infinite loops. Hopefully this example makes it clear:

我需要转储第3个表的视图，完全展开每个组的所有成员，包括嵌套组，转储组路径，以及处理无限循环。希望这个例子清楚地表明：

1. users

| ID | user_name |
|----|-----------|
|  1 | one       |
|  2 | two       |
|  3 | three     |
|  4 | four      |
|  5 | five      |

2. groups

| ID | group_name |
|----|------------|
|  6 | six        |
|  7 | seven      |
|  8 | eight      |
|  9 | nine       |
| 10 | ten        |

3. group members

| group_ID | member_ID |
|----------|-----------|
|        6 |         1 |
|        6 |         2 |
|        6 |         3 |
|        7 |         4 |
|        7 |         5 |
|        8 |         1 |
|        8 |         9 |
|        8 |        10 |
|        9 |         5 |
|       10 |         1 |
|       10 |         8 |

4. output/result

| group_ID | user_ID | user_name   | group_path
|----------|---------|-------------|------------
|        6 |       1 | one         | six
|        6 |       2 | two         | six
|        6 |       3 | three       | six
|        7 |       4 | four        | seven
|        7 |       5 | five        | seven
|        8 |       1 | one         | eight
|        8 |       5 | five        | eight > nine
|        8 |       1 | one         | eight > ten
|        8 |         | [recursive] | eight > ten > eight
|        9 |       5 | five        | nine
|       10 |       1 | one         | ten
|       10 |       1 | one         | ten > eight
|       10 |       5 | five        | ten > eight > nine
|       10 |         | [recursive] | ten > eight > ten

1 个解决方案

#1

Here's an answer and a live demo

这是一个答案和一个现场演示

;with cte as 
(
select id,name=user_name, type='U' from users
union
select  id, name=group_name, type='G' from groups
)
, cte2 as 
(
    select 
        user_id=c.id, 
        user_name=c.name,
        group_Id=g.group_ID,
        group_path= cast(c2.name as nvarchar(max))
    from cte c 
    left join [group members] g 
        on g.member_id=c.id and type='U'
    left join cte c2  
        on c2.type='G' and c2.id=g.group_ID
    union all
    select 
        user_id=user_id, 
        user_name=user_name,
        group_Id=g.group_ID,
        group_path= concat(c2.name,'>',c.group_path)
    from cte2 c
        join [group members] g 
        on g.member_id=c.group_Id
       join cte c2
       on g.group_ID=c2.id and c2.type='G'
        where c.group_path not like '%'+c2.name+'%'  
 )
 select 
     group_id,
     user_id,
     user_name, 
     group_path 
 from cte2
 where group_id is not null
 union all
 select 
     group_id=g.group_ID,
     user_id= NULL,
     user_name='[recursive]',
     group_path=concat(c2.name,'>',c.group_path)
 from cte2 c 
 join [group members] g
     on g.member_id=c.group_Id
     join cte c2
       on g.group_ID=c2.id and c2.type='G'
        where c.group_path like '%'+c2.name+'%'
  order by group_id,user_id

#1