oracle通过指定根节点和叶节点进行分层查询

Question

我有一个oracle表，其中包含以下字段MgrID、EmpID和Name。

层次结构实际上是从CEO级别开始的，但我想将CIO的EmpID指定为特定员工的根和EmpID，并获得该员工的层次结构。如何在Oracle中构建该查询？

Answer 1

在orcale中，您可以使用以开头并通过连接的select

 select  * from your_table  
 start with EmpID = yuor_value
 connect by prior your_child = your_parent;

Answer 2

Oracle设置:

CREATE TABLE Table_name (
  EmpID INT PRIMARY KEY,
  MgrID INT REFERENCES Table_name ( EmpID ),
  Name  VARCHAR2(10),
  Role  VARCHAR2(10)
);
INSERT INTO table_name
SELECT 1, NULL, 'Amy',     'CEO'      FROM DUAL UNION ALL
SELECT 2, 1,    'Bob',     'Director' FROM DUAL UNION ALL
SELECT 3, 1,    'Carol',   'Director' FROM DUAL UNION ALL
SELECT 4, 2,    'Deb',     'CIO'      FROM DUAL;

查询：

SELECT *
FROM   table_name
START WITH role = 'CIO'
CONNECT BY PRIOR MgrID = EmpID;

输出：

     EMPID      MGRID NAME       ROLE     
---------- ---------- ---------- ----------
         4          2 Deb        CIO        
         2          1 Bob        Director   
         1            Amy        CEO