表中的自连接和递归选择

Assuming a table as below

假设一个表如下所示。

| ID |  NAME | ROLE | MGRID |
---------------------------
|  1 |   ONE |    5 |   5 |
|  2 |   TWO |    5 |   5 |
|  3 | THREE |    5 |   6 |
|  4 |  FOUR |    5 |   6 |
|  5 |  FIVE |   15 |   7 |
|  6 |   SIX |   25 |   8 |
|  7 | SEVEN |   25 |   7 |
|  8 | EIGHT |    5 |   8 |

How do I get a list of all employees reporting to an employee, including the ones who are in subsequent reporting levels below?

如何获得向员工报告的所有员工的列表，包括以下级别的员工?

I mean, given emp id 5, I should get [1, 2] and given 7, I should get [1, 2, 5, 7]. How do I get this done?

我的意思是，给定emp id 5，我应该得到[1,2]，给定7，我应该得到[1,2,5,7]。我该怎么做呢?

Will self joins be of help here? Need to brush up my knowledge on joins now.

赛尔夫会在这里帮忙吗?现在需要温习一下加入的知识。

3 个解决方案

#1

Here is a SQL statement using Oracle.

下面是一个使用Oracle的SQL语句。

select  id, name, role, mgrID
from employees
start with id = 7
connect by NoCycle prior id = mgrid;

Please note that the manager for employee 7 is the employee 7 - they are their own manager. This will cause an error - "Connect By loop in user data'. By using the NoCycle keyword you can tell Oracle to detect this and avoid the error.

请注意雇员7的经理是雇员7 -他们是他们自己的经理。这将导致错误——“通过循环在用户数据中进行连接”。通过使用NoCycle关键字，您可以告诉Oracle检测这一点并避免错误。

Does this solve your issue?

这能解决你的问题吗?

#2

SELECT id
      FROM emp
START WITH id = 7
CONNECT BY NOCYCLE mgrid = PRIOR id

SQLFIDDLE LINK

SQLFIDDLE链接

#3

I know this isn't exactly what you were asking, but if you are willing to choose a finite number of level's to recurse it isn't too bad to write.

我知道这不是你想问的，但是如果你愿意选择有限的能级递归的话，写出来也不错。

SELECT table_2.id
FROM table LEFT JOIN 
  (table AS table_1 LEFT JOIN table AS table_2 ON table_1.id = table_2.MgrID)
ON table.id = table_1.MgrID
WHERE (((table.id)=7));

ETC.

等。

#1