CTE и рекурсивные CTE

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

CTE (Common Table Expression) — именованный временный результат, объявленный через WITH имя AS (...). Рекурсивный CTE ссылается сам на себя и обходит иерархии (дерево начальников, дерево комментариев) или строит последовательности.

Обычный CTE: разбиваем сложное на шаги

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

CREATE TABLE orders(id INTEGER PRIMARY KEY, customer TEXT, total INTEGER);
INSERT INTO orders VALUES
 (1,'Аня',500),(2,'Аня',300),(3,'Борис',100),(4,'Вика',900),(5,'Вика',50);

WITH customer_totals AS (
  SELECT customer, SUM(total) AS spent
  FROM orders
  GROUP BY customer
)
SELECT customer, spent
FROM customer_totals
WHERE spent > 400
ORDER BY spent DESC;

Вывод:

customer  spent
--------  -----
Вика      950
Аня       800

Тот же результат дал бы подзапрос в FROM, но WITH читается понятнее: сначала «посчитать суммы по клиентам», потом «оставить крупных». Несколько CTE можно перечислять через запятую и ссылаться одним на другой — так строят многошаговые отчёты без лестницы вложенных скобок.

Рекурсивный CTE: генерация последовательности

Синтаксис — WITH RECURSIVE. Он состоит из двух частей через UNION ALL: якорь (стартовая строка) и рекурсивный шаг (ссылается на сам CTE). Шаг повторяется, пока возвращает строки.

WITH RECURSIVE nums(n) AS (
  SELECT 1                              -- якорь: с чего начать
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5    -- шаг: пока n меньше 5
)
SELECT n FROM nums;

Вывод:

Условие WHERE n < 5 — это «тормоз». Без него рекурсия не остановится. Так генерируют ряды дат, числовые диапазоны, заготовки строк для отчёта.

Рекурсивный CTE: обход иерархии

Самое мощное применение — дерево. У сотрудников есть ссылка на начальника (boss_id). Развернём всю цепочку подчинения с уровнем вложенности.

CREATE TABLE staff(id INTEGER PRIMARY KEY, name TEXT, boss_id INTEGER);
INSERT INTO staff VALUES
 (1,'Директор',NULL),(2,'Руководитель A',1),(3,'Руководитель B',1),
 (4,'Инженер',2),(5,'Стажёр',4);

WITH RECURSIVE chain(id, name, boss_id, level) AS (
  SELECT id, name, boss_id, 0
  FROM staff WHERE boss_id IS NULL          -- якорь: верхушка дерева
  UNION ALL
  SELECT s.id, s.name, s.boss_id, c.level + 1
  FROM staff s JOIN chain c ON s.boss_id = c.id   -- шаг: подчинённые
)
SELECT level, name FROM chain ORDER BY level, id;

Вывод:

level  name
-----  --------------
0      Директор
1      Руководитель A
1      Руководитель B
2      Инженер
3      Стажёр

Якорь нашёл директора (нет начальника, level=0). Каждый шаг присоединяет тех, чей boss_id равен уже найденному id, увеличивая level. Так за один запрос разворачивается дерево любой глубины — для категорий, комментариев, BOM-структур.

Итог

WITH имя AS (...) — именованный шаг; делает сложные запросы читаемыми, заменяет вложенные подзапросы.
WITH RECURSIVE = якорь UNION ALL рекурсивный шаг; обязательно условие остановки.
Рекурсивный CTE обходит иерархии (дерево начальников/категорий) и генерирует последовательности.