sql-server - 递归选择

Question

我尝试使用 WITH 创建一个 SQL 语句，但我做错了。

我得到了以下表格；

组(ID、名称)

1, 'My app'
2, 'Local admins'
3, 'Global admins'

用户(1，姓名)

1, 'Arne'

GroupOwners (GroupId, OwnerType, OwnerId)

1, 'Group', 2

GroupMembers (GroupId, MemberType, MemberId)

2, 'Group', 3
3, 'User', 1

我正在尝试查找 Arne 所属的所有组。在本例中，它是 My App。但问题是 Local Admins 被设置为所有者。 Global admins 是 Local admins 的成员。 Arne 终于成为了 Global admins 的成员。

别管钻石了，它们是不正确的

嵌套因组而异。 (一些用户直接在 GroupOwners 中，而另一些可能有一个以用户为成员的组)

是否可以用一个SQL语句的母体来解决？附加约束:Groups/Users 在系统的其他地方使用。

Answer 1

这是递归的 cte，它首先会找到 Arno 所属的组，然后匹配直接或间接包含这些组的所有组。结果最终加入到 GroupOwners 以限制结果。注意:如果 Arno 可能是某个组的所有者，但不是该组的成员，则此查询还需要 UNION 来追加这些组。

declare @UserID int = 1

; with allTheGroups as (
  select gm.GroupID
    from GroupMembers gm
   where gm.MemberType = 'User'
     and gm.MemberID = @UserID
  union all
  select gm.GroupID
    from GroupMembers gm
   inner join allTheGroups
      on gm.MemberID = allTheGroups.GroupID
   where gm.MemberType = 'Group'
)
select Groups.*
  from Groups
 inner join GroupOwners gow
    on Groups.ID = gow.GroupID
   and gow.OwnerType = 'Group'
 inner join allTheGroups a
    on gow.OwnerID = a.GroupID

Sql Fiddle with example is here .

联合示例检索直接拥有所添加组的用户。需要附加到上面的查询(当然还要调整选择列表)。

union
select gow.OwnerID, Users.Name
  from GroupOwners gow
 inner join Users
    on gow.OwnerID = Users.ID
   and gow.OwnerType = 'User'
 where gow.OwnerID = @UserID