Рекурсивный CTE: простое руководство

Если вы хоть раз пытались построить иерархию в базе данных, то наверняка слышали про CTE – общие табличные выражения. Рекурсивный CTE позволяет решить задачи, где данные «схлопываются» в дерево: категории, сотрудники‑подчинённые, вложенные комментарии и т.д. Главное – понять, из чего состоит запрос и как не попасть в бесконечный цикл.

Как работает рекурсивный CTE

Запрос делится на две части: начальное (anchor) и рекурсивное (recursive). Первая часть выбирает корневые строки – те, у которых нет родителя. Вторая часть берёт уже найденные строки и присоединяет к ним дочерние элементы. Всё это заключено в оператор WITH RECURSIVE (или просто WITH в некоторых СУБД). После каждой итерации набор строк увеличивается, пока не закончатся новые дочерние записи. Чтобы избежать бесконечного цикла, обычно добавляют условие WHERE с проверкой уровня или ограничивают количество повторений через OPTION (MAXRECURSION) в MS SQL Server.

Практические примеры

Самый простой пример – вывод дерева категорий. Предположим, есть таблица categories с полями id, name и parent_id. Запрос будет выглядеть так:

WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id, 1 AS lvl
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.lvl + 1
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT * FROM cat_tree ORDER BY lvl, name;

Первая часть (WHERE parent_id IS NULL) вытаскивает корневые категории. Вторая часть соединяется с уже найденными строками и добавляет их детей, одновременно считая уровень вложенности. Вывод покажет, какие категории находятся на каком уровне, и вы легко сможете построить меню в приложении.

Более продвинутый пример – список всех подчинённых сотрудника в иерархии компании. Таблица employees содержит id, name и manager_id. Нам нужен весь штат под руководством директора:

WITH RECURSIVE emp_tree AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL   -- предположим, это директор
    UNION ALL
    SELECT e.id, e.name, e.manager_id, et.depth + 1
    FROM employees e
    JOIN emp_tree et ON e.manager_id = et.id
)
SELECT name, depth FROM emp_tree ORDER BY depth, name;

Здесь depth показывает, на сколько уровней человек находится ниже директора. Такой запрос полезен для построения оргструктур, расчёта бонусов или оценки нагрузки менеджеров.

Помните, что рекурсивный CTE – мощный инструмент, но требует аккуратности. Всегда проверяйте, что базовый набор не пуст, иначе запрос сразу закончится без результата. Добавляйте ограничения по уровню, если таблица может содержать циклы, и тестируйте на небольших данных перед запуском на продакшн.

Теперь вы знаете, как написать рекурсивный CTE, зачем он нужен и где применять. Попробуйте адаптировать примеры под свою схему, и вы быстро сможете решать любые иерархические задачи в SQL.