CodeRang
Темы SQL

CTE SQL: WITH и рекурсивные запросы

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

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

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

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

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

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

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

Пред.След.Случайная
ЗаданиеОтправкиAI Mentor
Найдите команды с крупным фондом зарплат

В employees хранятся сотрудники и их зарплаты, а в teams — команды. Сначала посчитайте суммарный фонд зарплат каждой команды, затем отдельным CTE оставьте команды с фондом не меньше 200000. Верните team_name и payroll, отсортировав результат по фонду зарплат по убыванию.

Запрос 1PostgreSQLv16
WITH team_payroll AS (
    SELECT team_id, SUM(salary) AS payroll
    FROM employees GROUP BY team_id
), ranked AS (
    SELECT team_id, payroll FROM team_payroll WHERE payroll >= 200000
)
SELECT t.team_name, r.payroll
FROM ranked r JOIN teams t ON t.id = r.team_id
ORDER BY r.payroll DESC, t.team_name;
Отправить
Результатteamsemployees
Ответ принят

Как работает CTE SQL с конструкцией WITH

Первый CTE, team_payroll, решает одну конкретную задачу: группирует сотрудников по team_id и считает SUM(salary). На этом шаге нас ещё не интересуют названия команд. Результат — небольшая промежуточная таблица из идентификатора команды и суммы зарплат.

Второй CTE, ranked, читает уже не исходную таблицу, а результат team_payroll. Здесь остаются только команды с payroll >= 200000. Именно в этом удобство нескольких CTE подряд: каждый следующий шаг может работать с результатом предыдущего, поэтому запрос читается сверху вниз как последовательность преобразований.

Финальный SELECT присоединяет teams, чтобы заменить технический team_id на название команды. ORDER BY r.payroll DESC, t.team_name задаёт стабильный порядок. Тот же расчёт можно было спрятать во вложенный подзапрос, но при добавлении ещё двух-трёх этапов WITH обычно проще читать, проверять и менять.

SQLPostgreSQL v16
WITH team_payroll AS (
    SELECT team_id, SUM(salary) AS payroll
    FROM employees GROUP BY team_id
), ranked AS (
    SELECT team_id, payroll FROM team_payroll WHERE payroll >= 200000
)
SELECT t.team_name, r.payroll
FROM ranked r JOIN teams t ON t.id = r.team_id
ORDER BY r.payroll DESC, t.team_name;
Результат запроса
team_namepayroll
Delta370000
Atlas295000
Cipher225000

Типичные ошибки при использовании CTE SQL

01

CTE написан, но не используется

WITH только объявляет промежуточный результат. Если финальный SELECT не читает этот CTE напрямую или через другой CTE, вычисление не становится частью ответа. Частая ошибка — создать правильный шаг, а затем случайно снова обратиться к исходной таблице.

02

Фильтрация выполняется не на том этапе

Если условие зависит от агрегата, например SUM(salary), применять его к отдельным строкам до группировки нельзя. Сначала нужно получить сумму по группе, а затем фильтровать готовый результат через следующий CTE или HAVING.

03

В рекурсивном CTE нет корректного условия остановки

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

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

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

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

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

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

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

Что такое CTE SQL?

CTE, или common table expression, — это именованный результат подзапроса, объявленный через WITH. На него можно ссылаться дальше в основном запросе или в следующих CTE того же выражения.

Как использовать CTE SQL?

После WITH указывают имя CTE и запрос в круглых скобках, а затем обращаются к этому имени как к источнику данных. Несколько CTE перечисляются последовательно через запятую.

Чем CTE SQL отличается от подзапроса?

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

Можно ли использовать несколько CTE в SQL?

Да. После WITH можно объявить несколько CTE, разделив их запятыми. Следующий CTE может обращаться к предыдущему, если это допускает конкретная структура запроса.

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

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

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

Практика CTE SQL на задачах

Откройте SQL Arena и попробуйте сначала задачи с одним WITH, а затем переходите к нескольким CTE и рекурсивным запросам. Если нужен последовательный маршрут, продолжите в треке «CTE: запрос из нескольких шагов».

Решать задачи на CTE

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