表中的自联接和递归选择 [英] Self join and recursive selection in a table

问题描述

假设如下表

| 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?

我的意思是，给定emp id 5，我应该得到[1，2]，给定7，我应该得到[1,2,5,7].我该如何完成?

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?

自我加入会在这里有所帮助吗?现在需要重新整理关于联接的知识.

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

推荐答案

以下是使用Oracle的SQL语句.

Here is a SQL statement using Oracle.

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

请注意，员工7的经理是员工7-他们是他们自己的经理.这将导致错误-用户数据中的按循环连接".通过使用NoCycle关键字，您可以告诉Oracle检测到该错误并避免该错误.

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.

这可以解决您的问题吗?

Does this solve your issue?

这篇关于表中的自联接和递归选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！