Рекурсия в Oracle

#sql #oracle #рекурсия

Столкнулся с такой задачей, не знаю как её выполнить
Есть таблица employees в ней поля

ID
NAME
CHIEF
BONUS


Поле CHIEF вторичный ключ ссылается на эту же таблицу с ID.
Нужно найти сотрудников, которые получают больше премии чем их руководитель.
    

Ответы

Ответ 1

Была когда-то примерно такая же задача на собеседовании в своё время, решается примерно
так (рекурсия тут не нужна):

SELECT EMP1.ID, 
       EMP1.NAME
FROM   employees EMP1 
JOIN   employees EMP2
ON     EMP1.CHIEF = EMP2.ID
WHERE  EMP1.BONUS > EMP2.BONUS


Ответ 2

Задачу можно решить несколькими способами:
Самое разумное решение через join:

select e1.*
from   employees e1 
inner  join employees e2
on     e1.chiff = e2.id
where  e1.bonus > e2.bonus


Так же можно использовать Connect by, рекурсивный with или скалярные подзапросы.

connect by.
Если развернуть вложенный запрос, то будет видна логика
Получается примерно так:

with employees as (
  select 1 as ID, 'a' as name, null as CHIFF, 100 as Bonus from dual union all
  select 2 as ID, 'b' as name, '1' as CHIFF, 50 as Bonus from dual union all
  select 3 as ID, 'c' as name, '1' as CHIFF, 200 as Bonus from dual union all
  select 4 as ID, 'd' as name, '0' as CHIFF, 100 as Bonus from dual unioт all
  select 5 as ID, 'e' as name, '3' as CHIFF, 400 as Bonus from dual union all
  select 6 as ID, 'f' as name, '3' as CHIFF, 200 as Bonus from dual
)
select distinct 
       t.id, 
       t.name, 
       t.chiff, 
       t.bonus, 
       priorbonus as chiff_bonus 
from  (
        select lpad(' ', 3*level)||e.id as tree, e.*, 
               prior id, 
               prior bonus  
        from   employees e 
        connect by prior id=e.chiff
        and    prior bonus < bonus 
) t where priorid is not null


Помните, что рекурсия может привести к неожиданным результатам и очень долго работающим
запросам.

Второй способ скалярные подзапросы. Примерно так:

select * 
from   (
         select e.*, 
                (select max(bonus) from employees e1 where e.chiff=e1.id) as chiff_bonus 
         from   employees e 
) t where t.bonus>chiff_bonus


max(bonus) для перестраховки