Условие

Таблица employees(id, name, manager_id). Для каждого сотрудника вывести его уровень иерархии (CEO = 0) и полный путь от CEO до него.

Решение

Recursive CTE

WITH RECURSIVE org AS (
    -- Anchor: CEO (manager_id IS NULL)
    SELECT
        id,
        name,
        manager_id,
        0 AS level,
        name AS path,
        ARRAY[id] AS id_path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- Recursive: подчинённые
    SELECT
        e.id,
        e.name,
        e.manager_id,
        o.level + 1,
        o.path || ' > ' || e.name,
        o.id_path || e.id
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT id, name, level, path FROM org
ORDER BY id_path;

Защита от циклов

Если в данных есть цикл (A → B → A), recursion бесконечная. Защита через массив посещённых:

WITH RECURSIVE org AS (
    SELECT id, manager_id, 0 AS level,
           ARRAY[id] AS visited
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id, o.level + 1,
           o.visited || e.id
    FROM employees e
    JOIN org o ON e.manager_id = o.id
    WHERE NOT (e.id = ANY(o.visited))   -- не повторяемся
)

Все подчинённые конкретного менеджера

WITH RECURSIVE subs AS (
    SELECT id, name FROM employees WHERE id = 42  -- стартовый менеджер
    UNION ALL
    SELECT e.id, e.name
    FROM employees e JOIN subs s ON e.manager_id = s.id
)
SELECT * FROM subs WHERE id != 42;

Размер каждого «домена» (число подчинённых)

WITH RECURSIVE subs AS (
    SELECT id AS root_id, id, name FROM employees
    UNION ALL
    SELECT s.root_id, e.id, e.name
    FROM employees e JOIN subs s ON e.manager_id = s.id
)
SELECT root_id, COUNT(*) - 1 AS team_size
FROM subs
GROUP BY root_id;

- 1 чтобы не считать самого менеджера.

Производительность

Recursive CTE на 100k+ сотрудников может быть медленным.
В PostgreSQL: CYCLE/SEARCH clauses (с 14 версии).
Альтернатива: closure table (предвычисленная таблица всех ancestor-descendant пар).
В ClickHouse — recursive CTE поддерживается с 24.0; до этого — приходилось через циклы в приложении.

SQL Server / MySQL

SQL Server: WITH RECURSIVE без слова RECURSIVE, просто WITH.
MySQL 8+: поддерживает WITH RECURSIVE. Старые версии — нет.

Подводные камни

Без anchor → infinite recursion → query fails (max recursion).
UNION (без ALL) — медленнее, теряет дубликаты, что обычно не нужно.
manager_id = id (self-reference) даёт цикл — нужна явная защита.
ORDER BY внутри recursive part игнорируется — сортируйте на финальном SELECT.
Path как string растёт; для глубоких деревьев — массив быстрее.

Эталонный ответ

WITH RECURSIVE org AS (anchor UNION ALL recursive_step). Anchor — CEO с manager_id IS NULL. Recursive — JOIN employees e ON e.manager_id = o.id. Защита от циклов через NOT id = ANY(visited).

Сценарий: recursive CTE для иерархии оргструктуры