当表具有指向自身的链接时的 SQL 查询

Question

嗨，我有一张 table :

Person:
PersonId
Name
ManagerId

所以 ManagerId 是对另一个人的引用。

所以数据库中可能有一个人:

1
Bob
null

2
Steve
1

3
Tim
2

所以鲍勃是史蒂夫的经理，史蒂夫是蒂姆的经理。

所以我想做的是编写一个查询，让所有的人都在 Bob 的管理之下。无论是直接的还是间接的。所以我想要史蒂夫和蒂姆。在同一条线上。

如果我写:

select * from Person
where ManagerId = 1 I would get only Steve.

我该怎么写才能让所有人直接或间接地服从 Bob？

Answer 1

您可以使用公用表表达式 (CTE) 来解决此问题。正如 Andrei 指出的那样，CTE 可用于递归(请参阅 Andrei 在他的帖子中包含的优秀引用资料)。假设您有一张表，如下所示:

create table Person
(
   PersonId int primary key,
   Name varchar(25),
   ManagerId int foreign Key references Person(PersonId)
)

让我们将以下数据插入表中:

insert into Person (PersonId, Name, ManagerId) values 
    (1,'Bob', null),
    (2, 'Steve',1),
    (3, 'Tim', 2)
    (4, 'John', 3),
    (5, 'James', null),
    (6, 'Joe', 5)

那么我们想要一个查询，该查询将返回直接或间接向 Bob 报告的每个人，即 Steve、Tim 和 John。我们不想返回 James 和 Bob，因为他们没有向任何人报告，或者 Joe，因为他向 James 报告。这可以通过 CTE 查询来完成，如下所示:

WITH Managers AS 
( 
     --initialize
     SELECT PersonId, Name, ManagerId  
        FROM Person WHERE ManagerId =1
     UNION ALL 
     --recursion 
     SELECT p.PersonId, p.Name, p.ManagerId 
        FROM Person p INNER JOIN Managers m  
        ON p.ManagerId = m.PersonId 
) 
SELECT * FROM Managers

此查询返回正确的结果:

PersonId    Name                      ManagerId
----------- ------------------------- -----------
2           Steve                     1
3           Tim                       2
4           John                      3

编辑:假设 OP 使用 SQL Server 2005 或更高版本，则此答案有效。我不知道这个语法在 MySQL 或 Oracle 中是否有效。