WITH
Задаёт именованный подзапрос (общее табличное выражение, CTE), к которому основной запрос обращается как к таблице.
WITH [RECURSIVE] name_cte [(column[, ...])] AS (subquery) main_queryПараметры
- RECURSIVEключевое словонеобязательный
- Разрешает табличному выражению ссылаться на само себя
- name_cteимя
- Имя табличного выражения
- columnимянеобязательный
- Имена столбцов табличного выражения, по умолчанию берутся из subquery
- subqueryподзапрос
- Подзапрос, результат которого доступен по имени name_cte
- main_queryподзапрос
- Основной запрос, который обращается к name_cte как к таблице
Возвращаемое значение
Временная именованная выборка, доступная только в этом запросе
Примеры
Статусы, где больше одного человека
WITH status_counts AS (
SELECT status,
COUNT(*) AS members_count
FROM FamilyMembers
GROUP BY status
)
SELECT status,
members_count
FROM status_counts
WHERE members_count > 1
ORDER BY status;Числа от 1 до 5 через RECURSIVE
WITH RECURSIVE numbers (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 5
)
SELECT n
FROM numbers;Подчинённые руководителей по уровням
WITH RECURSIVE subordinates AS (
SELECT id,
name,
manager_id,
1 AS LEVEL
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id,
e.name,
e.manager_id,
s.level + 1
FROM employees AS e
JOIN subordinates AS s ON e.manager_id = s.id
)
SELECT id,
name,
LEVEL
FROM subordinates
ORDER BY LEVEL,
id;Подробности
В одном WITH можно задать несколько выражений через запятую, и каждое может ссылаться на предыдущие.
Рекурсивное выражение (WITH RECURSIVE) состоит из двух частей, соединённых через UNION ALL или UNION. Начальный запрос выполняется один раз и даёт первые строки. Рекурсивная часть ссылается на само выражение и выполняется снова и снова: на каждом шаге она видит только строки, добавленные на прошлом шаге. Работа заканчивается, когда шаг не добавил ни одной строки.
Ограничения на число шагов нет, поэтому в рекурсивной части нужно условие остановки, например WHERE n < 5. UNION отбрасывает повторяющиеся строки, и это тоже может остановить рекурсию, а UNION ALL оставляет все.