CodeRang
Темы SQL

WITH RECURSIVE: рекурсивный CTE в SQL

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

Обновлено · Команда SQL Arena

Трек «CTE: запрос из нескольких шагов»Все задачи

Что потренируете

  • Разделять якорную и рекурсивную части CTE
  • Обходить иерархию сверху вниз
  • Считать уровень вложенности
  • Строить путь от корня до текущего узла
  • Понимать, где нужна защита от циклов

Пример задачи в тренажёре

Так выглядит задача в SQL Arena: условие, редактор, результат и схема базы.

Пред.След.Случайная
ЗаданиеОтправкиAI Mentor
Постройте оргструктуру под CTO

В таблице employees поле manager_id указывает на руководителя сотрудника. Начиная с сотрудника с id = 2, выведите его самого и всех подчинённых на любой глубине. Верните employee_name, depth и path, где depth равен 0 для начальной строки, а path показывает цепочку имён от CTO до сотрудника.

Запрос 1PostgreSQLv16
WITH RECURSIVE org AS (
  SELECT id, employee_name, 0 AS depth, ARRAY[employee_name]::text[] AS path FROM employees WHERE id = 2
  UNION ALL
  SELECT e.id, e.employee_name, o.depth + 1, o.path || e.employee_name
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT employee_name, depth, array_to_string(path, ' > ') AS path
FROM org
ORDER BY depth, employee_name;
Отправить
Результатemployeesemployee_badges
Ответ принят

Как работает WITH RECURSIVE в SQL

Внутри org есть две части. Первая — якорь. Она выполняется один раз и выбирает Leo по id = 2. Здесь же появляются вычисляемые поля: depth начинается с 0, а path создаётся как массив с одним именем. Именно набор строк якоря определяет, откуда начнётся обход дерева.

После UNION ALL идёт рекурсивная часть. PostgreSQL берёт строки, уже полученные на предыдущем шаге, и ищет сотрудников, у которых e.manager_id совпадает с o.id. Для найденной строки глубина увеличивается на единицу, а имя добавляется в конец path. На следующей итерации тот же принцип применяется уже к Omar и Sara, поэтому запрос доходит до Emma, Ivan и Noah без заранее известного числа уровней.

Финальный SELECT преобразует массив path в читаемую строку через array_to_string. ORDER BY depth, employee_name нужен не для самой рекурсии, а для стабильного вывода результата. В реальной модели стоит отдельно подумать о циклах: если A ссылается на B, а B снова приводит к A, рекурсия может ходить по кругу. Один из способов защиты — хранить посещённые id в пути и не добавлять уже встречавшийся узел.

Рекурсивный CTE состоит как минимум из двух частей: начального запроса, который называют якорным, и рекурсивного запроса, обращающегося к результату самого CTE. Обычно части соединяют через UNION ALL. На каждой итерации рекурсивная часть получает строки предыдущего шага и находит следующие. Так можно проходить дерево категорий, структуру подразделений или другие иерархии неизвестной заранее глубины. Важно задать условие, при котором новые строки перестанут появляться: иначе ошибочная логика может привести к циклическому обходу или чрезмерному числу итераций.

SQLPostgreSQL v16
WITH RECURSIVE org AS (
  SELECT id, employee_name, 0 AS depth, ARRAY[employee_name]::text[] AS path FROM employees WHERE id = 2
  UNION ALL
  SELECT e.id, e.employee_name, o.depth + 1, o.path || e.employee_name
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT employee_name, depth, array_to_string(path, ' > ') AS path
FROM org
ORDER BY depth, employee_name;
Результат запроса
employee_namedepthpath
Leo0Leo
Omar1Leo > Omar
Sara1Leo > Sara
Emma2Leo > Omar > Emma
Ivan2Leo > Omar > Ivan
Noah2Leo > Sara > Noah

Ошибки в рекурсивных CTE и SQL-запросах

01

Связывать рекурсивную часть не с предыдущим уровнем

Если внутри рекурсивной части снова соединить employees только с фиксированным id корня, запрос будет каждый раз находить один и тот же первый уровень. Для движения вниз условие должно связывать новую строку с результатом самого рекурсивного CTE.

02

Использовать UNION вместо UNION ALL без причины

UNION удаляет дубликаты на каждом шаге и добавляет лишнюю работу. Для обычного обхода дерева чаще нужен UNION ALL. Удаление повторов само по себе также не заменяет осознанную защиту от циклических связей.

03

Считать, что порядок обхода гарантирован

Порядок, в котором СУБД вычисляет рекурсивные строки, не является порядком результата. Если нужен вывод по глубине, пути или имени, задайте его явно через ORDER BY во внешнем SELECT.

Задачи по теме в тренажёре

Трек «CTE: запрос из нескольких шагов»

После базового обхода дерева полезно усложнить задачу: считать всю команду руководителя, определять глубину сотрудника, подниматься от узла к родителям или строить хлебные крошки. Такие упражнения заставляют менять направление рекурсии и состояние, которое переносится между шагами. В SQL Arena есть задачи на полный состав команды, глубину ветки, движение вверх по классификатору и построение пути. Для системной практики продолжите в треке «CTE: запрос из нескольких шагов». В тренажёре 1 192 задачи, а ответ проверяется на скрытых данных, поэтому запрос должен работать не только на строках, которые видны в условии.

Справочник по теме

Подробные статьи с синтаксисом и примерами в справочнике SQL Arena.

Частые вопросы

Что такое WITH RECURSIVE в SQL?

WITH RECURSIVE объявляет CTE, который может ссылаться на собственный результат. Обычно он содержит якорную часть для начальных строк и рекурсивную часть, которая на каждой итерации получает следующие строки.

Что такое рекурсивный CTE?

Рекурсивный CTE — это общее табличное выражение, результат которого формируется итерациями. Оно удобно для деревьев, иерархий, цепочек связей и других задач, где количество уровней заранее неизвестно.

Как написать рекурсивный запрос SQL?

Сначала задают начальный набор строк, затем через UNION ALL добавляют запрос, который обращается к CTE и получает следующий уровень данных. Рекурсия заканчивается, когда очередная итерация больше не возвращает новых строк.

Чем рекурсивный CTE отличается от обычного CTE?

Обычный CTE вычисляется как отдельный именованный результат и не использует сам себя. Рекурсивный CTE ссылается на собственные строки и поэтому может последовательно проходить неизвестное заранее количество уровней.

Смотрите также

Практика WITH RECURSIVE и рекурсивных CTE

В SQL Arena можно писать PostgreSQL прямо в браузере и сразу проверять запрос. Бесплатно доступны 292 задачи без карты; сложные задачи открываются в Premium. Для рекурсивных запросов особенно полезно проверять себя на разных схемах, потому что ошибка часто проявляется только на дополнительном уровне дерева или другой структуре данных.

Решать задачи по SQL

Другие тренажёры CodeRang