递归SQL查询，但不是通常的递归类型

Question

我有一组表，可以将一群人组织成团队。

• 用户（ID int PK等）
• 团队（ID int PK等）
• UsersToTeams（ID int PK，UserID int FK，TeamID int FK，TeamSupervisor位不为空）

表中没有父ID，因为用户可以在任意数量的团队中，团队可以有任意数量的主管。一个用户可能在六个团队中，但只监督其中两个团队，其中一个或两个团队中可能都有其他主管。因此，我的层次结构看起来更像一个网络，而不是一棵树。

我认识到递归查询可能会导致循环引用。假设软件目前正在处理这个问题。

公司的层次结构是由一个主管监督一个用户团队、一个经理监督一个主管团队等来描述的。所以它是分层的，但不是以通常的方式。

我需要一个查询，给定一个UserID，它将返回他所监督的用户的ID，向下到无限级别。这样的查询会如何进行？

示例

Users (ID, Name)
1 Archie
2 Betty
3 Chuck
4 Dilton
5 Eddie
6 Fannie

用户1是经理（级别3）。用户2和3是主管（级别2）。用户4、5、6是用户（级别1）。

Teams (ID, Name)
1 Team Alpha
2 Team Bravo
3 Sup Team
UsersToTeams (ID INT PK, UserID INT FK, TeamID INT FK, isSupervisor BIT)
1 1 3 1  -- Archie supervises Sup Team
2 2 3 0  -- Betty is a member of Sup Team
3 3 3 0  -- Chuck is a member of Sup Team
4 2 1 1  -- Betty supervises team Alpha
5 4 1 0  -- Dilton is a member of team Alpha
6 5 1 0  -- Eddie is a member of team Alpha
7 3 2 1  -- Chuck supervises Team Bravo
8 6 2 0  -- Fannie is a member of Team Bravo

• Archie是一名经理，负责管理一个主管团队
• Betty是一名主管，负责管理一个用户团队
• Chuck是一名主管，负责管理一个用户团队
• Betty和Chuck也是Archie团队的成员，但不负责监督

因此：

1. 如果我通过UserID 5（Eddie），我应该只返回5，因为Eddie不监督任何人
2. 如果我通过了UserID 3（Chuck），我应该会得到3和6，因为Fannie在Chuck监督的团队中
3. 如果我输入UserID 1（Archie），我应该取回这里描述的所有UserID，因为Betty和Chuck在Archie的团队中，其他人都在Betty的团队或Chuck的团队中

抱歉，我尝试了SQL fiddle链接，但在"构建架构"15分钟后，我对它失去了希望。

Answer 1

您可以使用递归CTE来完成此操作。

首先，选择用户自己，然后递归地选择他立即监督的所有用户：

declare @userID int = 1;
with u as (
  select id from users where id = @userID
  union all
  select lacky.userID from u supervisor
  join usersToTeams supervising on supervising.userID = supervisor.id and isSupervisor = 1
  join usersToTeams lacky on lacky.teamID = supervising.teamID and lacky.isSupervisor = 0
)
select * from u

小提琴在这儿：http://www.sqlfiddle.com/#！3/525e1/3