具有多个表的SQL Server递归

我试图让用户成为组的一部分,但组也可以是组的成员。因此,问题在于谁能够“有效”地接触到某些群体。我见过很多在SQL Server上使用CTE的例子,这似乎是同一个例子,但所有的例子都很简单,比如组织结构图。在我的例子中,我必须连接多个表,只是无法掌握整个递归过程。

这些表格是:

usergroup :哪些用户拥有哪些组

uid int
gid int

groupgroup :哪些组给哪些组

parentid int
childid int

group :许多组都在一个组中 sourceid .

gid int
sourceid int
name varchar

例如:

usergroup

uid   gid
------------
1     101
1     102
2     102
2     120

groupgroup

parentid  childid
-------------------
103       101
110       103
120       110

group

gid   sourceid    name
-----------------------
101   5           group101
102   5           group102
103   5           group103
110   8           group110
120   8           group120

parentid和childid之间的区别在于parentid授予childid。因此,如果你是parentid的成员,你就可以有效地访问childid。正如预期的那样,parentid和childid将存在于组表中。

我的目标是简单地获得一个列表,列出所有有权访问给定源(sourceid)中哪些组的用户。如果没有嵌套,sourceid为5就很简单了:

select *
from usergroup
inner join group on group.gid = usergroup.gid
where group.sourceid = 5

这个查询会告诉我用户1有组101、102,用户2有102。

但问题是,用户可能有一个父gid,通过任何级别的嵌套给出子id,而这些子id可能是源id为5的gid。

相反,我希望用户1仍然直接拥有101和102,用户2直接拥有102 AND 101和103因为嵌套 .

在基本层面上,我只需要知道用户XYS在源代码中直接或通过嵌套和标志或其他标记(如果是嵌套还是直接)拥有ABC组。如果我们能得到一个级别(0=直接,1=上一级,等等……),那就更好了。

我可以在Java代码中通过制作一个大的映射/列表和循环来实现这一点,但有没有一种直接从SQL Server获取它的优雅方法,而且可能比在代码中更快?

非常感谢。