Oracle中的员工经理层次结构查询

Question

我有一个结构如下的表：

+-------+-------+|EMPID | MGRID|+-------+-------+|A|B||B|C||C | D||I|J||J|D||K|L||L|O||O|D|+-------+-------+

我想构建一个查询，它可以创建如下视图，将所有管理器层次结构显示为从最高管理器开始的列，但我不确定如何在oracle中实现这一点。有人能帮忙建议怎么做吗。。

所需输出：

+-------+-------+------+------+------+|EMPID|MGRID|LVL1|LVL2|LVL3|+-------+-------+------+------+------+|A|B|D|C|B||B|C|D|C|||C|D|D||||I|J|D|J|||J|D|D||||K|L|D|O|L||L|O|D|O|||O|D|D|||+-------+-------+------+------+------+

注意：我不知道如何将表格添加到问题中，但将其添加为文本以便复制。

Answer 1

您可以使用包含其相关函数的分层查询，如SYS_CONNECT_BY_PATH()和SYS_CONNECT_BY_PATH()，包括条件聚合：

WITH emp2 AS
(
SELECT empid, mgrid, 
MAX( LENGTH(SYS_CONNECT_BY_PATH(empid, '|'))/2 + 1 ) 
OVER ( PARTITION BY empid , mgrid ) AS max_lvl, 
level AS lvl, CONNECT_BY_ROOT(mgrid) AS member
FROM emp
CONNECT BY PRIOR empid = mgrid
)
SELECT empid, mgrid, 
MAX( CASE WHEN max_lvl - lvl = 1 THEN member END ) AS LVL1,
MAX( CASE WHEN max_lvl - lvl = 2 THEN member END ) AS LVL2,
MAX( CASE WHEN max_lvl - lvl = 3 THEN member END ) AS LVL3
FROM emp2
GROUP BY empid, mgrid 
ORDER BY empid, mgrid;
+-------+-------+------+------+------+
| EMPID | MGRID | LVL1 | LVL2 | LVL3 |
+-------+-------+------+------+------+
| A     | B     | D    | C    | B    |
| B     | C     | D    | C    |      |
| C     | D     | D    |      |      |
| I     | J     | D    | J    |      |
| J     | D     | D    |      |      |
| K     | L     | D    | O    | L    |
| L     | O     | D    | O    |      |
| O     | D     | D    |      |      |
+-------+-------+------+------+------+

演示