как получить иерархию сотрудников без использования предложения connect by в oracle

#sql #oracle

Вопрос:

 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 |
 

идентификаторы сотрудников 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;
 

получение ошибки, такой как пропущенное ключевое слово

Комментарии:

1. Вы также можете использовать рекурсивный CTE.

2. Что вы пробовали до сих пор ? У вас были какие-нибудь ошибки ? Рекурсивные запросы-это то, что вы можете использовать для этого.

3. да, не получение желаемых выходных данных из рекурсивного запроса с предложением

4. @shubham — если вы отредактируете свой вопрос, чтобы включить в него свой запрос и вывод, который вы из него получите, мы можем (надеюсь) сказать вам, почему он не дает ожидаемых результатов.

5. конечно, только что добавлен запрос, но появляется ошибка в запросе @alexpool

Ответ №1:

получение ошибки, такой как пропущенное ключевое слово

Здесь нет RECURSIVE ключевого слова; CTE делается рекурсивным, обращаясь к самому себе в объединении. concat() Функция также принимает только два аргумента в Oracle, поэтому вам потребуется вложить вызовы или более просто использовать оператор объединения. Приведение идентификатора менеджера 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
 

Если вы просто получите идентификатор ребенка в рекурсивном члене, который можно использовать 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<>fiddle<> показывает рекурсивный CTE со всеми выводами, затем фильтруется и агрегируется, как указано выше; а также показывает эквивалент иерархического запроса для получения информации, и я включил вашу версию и исправленную версию (это не ошибка, но дает неправильные результаты, как с cast() и to_char() ).