#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()
).