Oracle SQL: Connect by для получения суммы зарплат всех подчинённых в иерархии
Все примеры ниже базируются на известной схеме SCOTT*.
В таблице EMP хранится инфо о работниках, в частности, salary и подчинённость. Колонка mgr это обратная ссылка на empno в той же таблице, указывает значение empno для непосредственного руководителя на 1 уровень выше этого сотрудника.
Допустим, стоит задача вывести для каждого работника ещё и сумму зарплат всех его подчинённых (с учетом и сумм подчиненных его подчиненным). Зачем это нужно IRL? Не знаю, за всё время работы с Оракелом в проде использовал connect by только для разбиения строки по разделителю – вот пример https://habr.com/ru/post/418271/. Был более подходящий пример на sql.ru, но, такая жизнь. И, конечно, для генерации последовательностей, вот пример https://habr.com/ru/post/269173/.
Но на собеседовании такая задача вполне может иметь место.
Если нужно вывести сумму зарплат для всех подчиненных самого большого босса King, пишем по учебнику:
--вывели босса и всех его подчиненных, уровень подчиненности lvl - просто для наглядности
select empno, ename, job, mgr, hiredate, sal, comm, deptno, level lvl from scott.emp e CONNECT BY PRIOR e.empno = e.mgr start with e.mgr is null
Посчитать теперь сумму – задача тривиальная.
Задача усложняется, если посчитать нужно для всех. Нагуглил аналог решения тут https://www.cyberforum.ru/post13607497.html
select e.*, (select sum (sal) from scott.emp s CONNECT BY s.mgr = PRIOR s.empno start with s.empno = e.empno) - e.sal DescendantSalary from scott.emp e --where e.empno in (select mgr from scott.emp) or e.empno is null
Нули в колонке DescendantSalary, очевидно, у работников, не имеющих подчиненных.
*Скрипт для создания схемы SCOTT и наполнения данными при желании можно взять здесь