递归分层Oracle SQL查询

Question

我有一个源表，如下所示：

Emp_ID| Name| Manager_ID
001|abc|005
005|cde|010
010|xyz|050
050 | bcg| 100
100|sta|NULL

我的要求是填充目标表，如下所示：

Emp_ID| Name| Manager_1| Manager_2| Manager_3| Manager_4
005|cde|xyz|bcg|sta|NULL
050|bcg|sta| NULL|NULL|NULL
100|sta|NULL|NULL|NULL
001|abc|cde|xyz|bcg|sta

请帮帮忙。

Answer 1

我认为下面的查询会对你有所帮助。但要将字符串拆分为单个经理id，您需要知道经理级别的最大数目。

WITH data_set AS
     (SELECT '001' emp_id, 'aaa' emp_name, '005' mgr_id
        FROM DUAL
      UNION
      SELECT '005' emp_id, 'bbb' emp_name, '010' mgr_id
        FROM DUAL
      UNION
      SELECT '010' emp_id, 'ccc' emp_name, '050' mgr_id
        FROM DUAL
      UNION
      SELECT '020' emp_id, 'ddd' emp_name, '050' mgr_id
        FROM DUAL
      UNION
      SELECT '050' emp_id, 'eee' emp_name, '100' mgr_id
        FROM DUAL
      UNION
      SELECT '100' emp_id, 'fff' emp_name, '200' mgr_id
        FROM DUAL
      UNION
      SELECT '200' emp_id, 'ggg' emp_name, NULL mgr_id
        FROM DUAL)
SELECT     emp_id, emp_name, mgr_id,
           LTRIM (SYS_CONNECT_BY_PATH (emp_id, '-'), '-') chain
      FROM data_set
START WITH mgr_id IS NULL
CONNECT BY mgr_id = PRIOR emp_id
  ORDER SIBLINGS BY emp_id;

Answer 2

如果您的层次结构仅扩展到4层深，则可以使用以下查询：

select t1.Emp_ID, 
       t1.Name, 
       t2.Name as Manager_1, 
       t3.Name as Manager_2, 
       t4.Name as Manager_3,
       t5.Name as Manager_4
from tmp t1
left join tmp t2 on t2.Emp_ID = t1.Manager_ID
left join tmp t3 on t3.Emp_ID = t2.Manager_ID
left join tmp t4 on t4.Emp_ID = t3.Manager_ID
left join tmp t5 on t5.Emp_ID = t4.Manager_ID;

Answer 3

透视选项：

SELECT * FROM
(
  SELECT emp_id, name, manager_id
  FROM employees
)
PIVOT
(
  COUNT(manager_id)
  FOR manager_id IN ('005', '100', '050')
)
ORDER BY emp_id;