如何在不使用oracle中的connectby子句的情况下检索员工层次结构

Question

create table test 
(
emp_id number ,
emp_name varchar2(10),
mgr_id number ) ;
insert into test values(1,'amar',null);
insert into test values(2,'ram',1);
insert into test values(3,'raju',2);
insert into test values(4,'vinod',3);
insert into test values(5,'amar1',null);
insert into test values(6,'ram1',5);
insert into test values(7,'raju1',6);
insert into test values(8,'vinod1',7);

现在我想要的是并没有经理的最高级别员工的层次结构。要求逗号分隔的员工列表

预期输出：

| emp_id | emp_id_under_manager |
|1      |    2,3,4 |
|5      |    6,7,8 |

员工id 1和5没有经理，因此只显示2名员工的层次

WITH RECURSIVE employee_paths (emp_id, emp_name, path) AS
(
SELECT e.emp_id, e.emp_name, CAST(e.emp_id AS CHAR(200))  
FROM test e 
WHERE mgr_id IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, CONCAT(ep.path, ',', e.emp_id)
FROM employee_paths ep JOIN test  e
ON ep.emp_id = e.mgr_id
)
SELECT * FROM employee_paths ORDER BY path;

获取错误，如丢失关键字

Answer 1

获取错误，如缺少关键字

没有RECURSIVE关键字；CTE是通过在并集中引用自身而递归的。concat()函数在Oracle中也只接受两个参数，因此您需要嵌套调用，或者更简单地使用串联运算符。将管理器ID强制转换为char(200)也意味着用空格填充，所以当连接时会有很大的间隙；可以使用to_char()而不是cast():

WITH employee_paths (emp_id, emp_name, path) AS
(
SELECT e.emp_id, e.emp_name, to_char(e.emp_id)
FROM test e 
WHERE mgr_id IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, ep.path || ',' || e.emp_id
FROM employee_paths ep JOIN test  e
ON ep.emp_id = e.mgr_id
)
SELECT * FROM employee_paths ORDER BY path;

但这仍然不能给你想要的输出。。。

EMP_ID EMP_NAME PATH   
------ -------- -------
1 amar     1      
2 ram      1,2    
3 raju     1,2,3  
4 vinod    1,2,3,4
5 amar1    5      
6 ram1     5,6    
7 raju1    5,6,7  
8 vinod1   5,6,7,8

如果你只在递归成员中获得子ID，你可以使用listagg()来获得逗号分隔的列表：

with rcte (root_id, emp_id) as (
select emp_id, emp_id
from test
where mgr_id is null
union all
select root_id, t.emp_id
from rcte r
join test t on t.mgr_id = r.emp_id
)
select root_id as emp_id,
listagg(emp_id, ',') within group (order by emp_id) as emp_id_under_manager
from rcte
where emp_id != root_id
group by root_id

你的样本数据得到的是：

EMP_ID EMP_ID_UNDER_MANAGER
------ --------------------
1 2,3,4
5 6,7,8

db<gt；fiddle显示了具有所有输出的递归CTE，然后如上所述进行过滤和聚合；还显示了信息的分层查询等价物，我已经包括了您的版本和更正的版本(这不会出错，但会给出错误的结果，都是cast()和to_char()(。