Дерево подчинения
В таблице staff у каждого сотрудника указан начальник (boss_id). Выведем всех сотрудников школы с уровнем: директор — 1, завучи — 2 и так далее.
WITH RECURSIVE tree(id, name, position, lvl) AS (
SELECT id, name, position, 1
FROM staff
WHERE boss_id IS NULL -- начинаем с директора
UNION ALL
SELECT s.id, s.name, s.position, t.lvl + 1
FROM staff s
JOIN tree t ON s.boss_id = t.id -- подчинённые тех, кто уже найден
)
SELECT lvl, name, position
FROM tree
ORDER BY lvl, name;
Результат:
| lvl | name | position |
| 1 | Ольга Николаевна | директор |
| 2 | Ирина Павловна | завуч |
| 2 | Сергей Петрович | завуч |
| 3 | Анна Викторовна | учитель математики |
| 3 | Мария Сергеевна | учитель русского языка |
| 3 | Наталья Ивановна | учитель истории |
| 3 | Павел Андреевич | учитель информатики |
| 4 | Дмитрий Олегович | лаборант |
На каждом шаге рекурсивная часть находит сотрудников, чей начальник (s.boss_id) был найден на предыдущем шаге (t.id).
Когда подчинённых больше нет, новых строк не появляется, и запрос завершается сам. Это верно, пока в данных нет цикла: если А начальник Б, а Б начальник А, запрос с UNION ALL не завершится.
Чтобы идти по дереву вверх, от сотрудника к начальникам, соединение разворачивают: ищут сотрудника, чей id равен boss_id строки предыдущего шага.
Коротко
WITH RECURSIVE имя AS (начальная часть UNION ALL рекурсивная часть).
- Рекурсивная часть ссылается на само CTE и повторяется, пока появляются новые строки.
- Рекурсия нужна для деревьев и цепочек неизвестной глубины и для последовательностей чисел.