Для указания общих выражений с таблицей используйте предложение WITH, имеющее одну или несколько подпредложений, разделенных запятыми. Каждое подпредложение предоставляет подзапрос, который создает набор результатов и связывает имя с подзапросом. В следующем примере в предложении WITH определены CTE с именами cte1 и cte2, и они ссылаются на них в верхнем уровневом предложении SELECT, которое следует за предложением WITH:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

В операторе, содержащем предложение WITH, каждое имя CTE может использоваться для доступа к соответствующему набору результатов CTE.

Имя CTE может ссылаться на другие CTE, что позволяет определять CTE на основе других CTE.

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

Общие выражения с таблицей — это необязательная часть синтаксиса операторов DML. Они определяются с помощью предложения WITH:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name называет одно общее выражение с таблицей и может использоваться как ссылка на таблицу в операторе, содержащем предложение WITH.

Часть subquery в AS (subquery) называется “подзапросом CTE” и генерирует набор результатов CTE. Скобки после AS обязательны.

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

Определение имен столбцов для данного CTE выполняется следующим образом:

Предложение WITH разрешается в следующих контекстах:

Разрешается только одно предложение WITH на одном уровне. WITH, за которым следует WITH на одном уровне, запрещено, поэтому это недопустимо:

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

Чтобы оператор стал допустимым, используйте одно предложение WITH, разделяя подпредложения запятыми:

WITH cte1 AS (...), cte2 AS (...) SELECT ...

Однако оператор может содержать несколько предложений WITH, если они находятся на разных уровнях:

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

Предложение WITH может определить одно или несколько общих выражений с таблицей, но каждое имя CTE должно быть уникальным в пределах предложения. Это недопустимо:

WITH cte1 AS (...), cte1 AS (...) SELECT ...

Чтобы оператор стал допустимым, определите CTE с уникальными именами:

WITH cte1 AS (...), cte2 AS (...) SELECT ...

CTE может ссылаться на себя или на другие CTE:

Для разрешения ссылок на объекты с одинаковыми именами производные таблицы скрывают CTE, а CTE скрывают базовые таблицы, TEMPORARY таблицы и представления. Разрешение имен происходит путем поиска объектов в том же блоке запроса, а затем по очереди во внешних блоках, пока не будет найден объект с указанным именем.

Дополнительные соображения по синтаксису, относящиеся к рекурсивным CTE, см. в разделе Рекурсивные общие выражения с таблицей.

Рекурсивное общее табличное выражение — это выражение, содержащее подзапрос, ссылающийся на собственное имя. Например:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

При выполнении оператора получается такой результат: один столбец, содержащий простую линейную последовательность:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

Рекурсивное общее табличное выражение имеет следующую структуру:

Подзапрос рекурсивного общего табличного выражения, показанный ранее, содержит эту нерекурсивную часть, которая извлекает одну строку для создания начального набора строк:

SELECT 1

Подзапрос CTE также содержит эту рекурсивную часть:

SELECT n + 1 FROM cte WHERE n < 5

На каждой итерации оператор SELECT генерирует строку с новым значением, на единицу большим, чем значение n из предыдущего набора строк. Первая итерация работает с начальным набором строк (1) и генерирует 1+1=2; вторая итерация работает с набором строк первой итерации (2) и генерирует 2+1=3; и так далее. Это продолжается до тех пор, пока рекурсия не закончится, что происходит, когда n больше не меньше 5.

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

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

В режиме SQL без жёстких ограничений оператор генерирует следующий вывод:

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

Значения столбца str равны 'abc', потому что нерекурсивный оператор SELECT определяет ширину столбцов. Следовательно, более широкие значения str, полученные рекурсивным оператором SELECT, обрезаются.

В режиме SQL с жёсткими ограничениями оператор генерирует ошибку:

ERROR 1406 (22001): Data too long for column 'str' at row 1

Чтобы решить эту проблему, чтобы оператор не генерировал обрезку или ошибки, используйте CAST() в нерекурсивном операторе SELECT, чтобы сделать столбец str шире:

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

Теперь оператор генерирует этот результат без обрезки:

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

К столбцам обращаются по имени, а не по позиции, что означает, что столбцы в рекурсивной части могут обращаться к столбцам в нерекурсивной части, которые имеют другую позицию, как иллюстрирует данное CTE:

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

Так как p в одной строке выводится из q в предыдущей строке, и наоборот, положительные и отрицательные значения меняют свои позиции в каждой последующей строке вывода:

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

Существуют некоторые ограничения синтаксиса внутри подзапросов рекурсивных CTE:

Эти ограничения взяты из стандарта SQL, за исключением упомянутых ранее особенностей MySQL.

Для рекурсивных CTE вывод оператора EXPLAIN показывает строки для рекурсивных SELECT частей в столбце Recursive.

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

Фактические затраты CTE также могут быть связаны с размером набора результатов. CTE, генерирующий много строк, может потребовать внутреннюю временную таблицу достаточно большого размера, чтобы быть преобразована из памяти в диск, и может пострадать от снижения производительности. В таком случае увеличение допустимого размера временных таблиц в памяти может улучшить производительность; см. Раздел 10.4.4, «Использование внутренних временных таблиц в MySQL».

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

Предположим, что рекурсивное CTE написано без условия прекращения рекурсии:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

По умолчанию, cte_max_recursion_depth имеет значение 1000, что приводит к прекращению CTE, когда он выполняет рекурсию более чем на 1000 уровней. Приложения могут изменить значение сеанса, чтобы настроить его под свои потребности:

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

Вы также можете установить глобальное значение cte_max_recursion_depth, чтобы оно повлияло на все последующие сеансы.

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

SET max_execution_time = 1000; -- impose one second timeout

В качестве альтернативы, включите указание оптимизатора в саму инструкцию CTE:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

Вы также можете использовать LIMIT внутри рекурсивного запроса, чтобы установить максимальное количество строк, возвращаемых во внешнюю SELECT, например:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

Это можно сделать дополнительно к установлению лимита времени или вместо него. Таким образом, следующее CTE завершается после возвращения десяти тысяч строк или выполнения в течение одной секунды (1000 миллисекунд), в зависимости от того, что произойдёт раньше:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

Если рекурсивный запрос без лимита времени выполнения входит в бесконечный цикл, его можно прервать из другого сеанса, используя KILL QUERY. В самом сеансе клиентская программа, используемая для выполнения запроса, может предоставить способ прерывания запроса. Например, в mysql нажатие Control+C прерывает текущую инструкцию.

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

Генерация ряда Фибоначчи

Ряд Фибоначчи начинается с двух чисел 0 и 1 (или 1 и 1), и каждое последующее число является суммой двух предыдущих. Рекурсивное общее табличное выражение может сгенерировать ряд Фибоначчи, если каждая строка, созданная рекурсивным SELECT, имеет доступ к двум предыдущим числам ряда. Следующее CTE генерирует ряд из 10 чисел, используя 0 и 1 в качестве первых двух чисел:

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

CTE производит следующий результат:

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

Как работает CTE:

Представленный выше результат показывает весь результат CTE. Чтобы выбрать только часть из него, добавьте соответствующую клаузу WHERE к основному SELECT. Например, чтобы выбрать 8-е число Фибоначчи, сделайте следующее:

mysql> WITH RECURSIVE fibonacci ...
       ...
       SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
|    13 |
+-------+

Генерация ряда дат

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

Предположим, что таблица продаж содержит следующие строки:

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 |  50.00 |
| 2017-01-08 |  10.00 |
| 2017-01-08 |  20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 |   5.00 |
+------------+--------+

Этот запрос обобщает продажи по дням:

mysql> SELECT date, SUM(price) AS sum_price
       FROM sales
       GROUP BY date
       ORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-06 |     50.00 |
| 2017-01-08 |    180.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

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

Вот CTE для генерации ряда диапазона дат:

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

CTE производит следующий результат:

+------------+
| date       |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+

Как работает CTE:

Объединение CTE с LEFT JOIN по таблице sales производит сводку продаж со строкой для каждой даты в диапазоне:

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

Вывод выглядит следующим образом:

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-04 |      0.00 |
| 2017-01-05 |      0.00 |
| 2017-01-06 |     50.00 |
| 2017-01-07 |      0.00 |
| 2017-01-08 |    180.00 |
| 2017-01-09 |      0.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

Некоторые замечания:

Обход иерархических данных

Рекурсивные общие табличные выражения полезны для обхода данных, образующих иерархию. Рассмотрим эти операторы, которые создают небольшой набор данных, показывающий для каждого сотрудника в компании имя сотрудника и его идентификатор, а также идентификатор руководителя сотрудника. Руководитель высшего звена (генеральный директор) имеет идентификатор руководителя NULL (нет руководителя).

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

Результирующий набор данных выглядит следующим образом:

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

Чтобы создать организационную структуру с цепочкой управления для каждого сотрудника (то есть путь от генерального директора к сотруднику), используйте рекурсивное CTE:

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

CTE производит следующий вывод:

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

Как работает CTE:

Чтобы найти путь для конкретного сотрудника или сотрудников, добавьте клаузу WHERE к основному SELECT. Например, чтобы отобразить результаты для Тарека и Сары, измените этот SELECT следующим образом:

mysql> WITH RECURSIVE ...
       ...
       SELECT * FROM employees_extended
       WHERE id IN (692, 4610)
       ORDER BY path;
+------+-------+-----------------+
| id   | name  | path            |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
|  692 | Tarek | 333,692         |
+------+-------+-----------------+

Общие табличные выражения (CTE) в некоторых отношениях похожи на производные таблицы:

Из-за этого сходства CTE и производные таблицы часто могут использоваться взаимозаменяемо. В качестве тривиального примера, эти операторы эквивалентны:

WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;

Однако CTE имеют некоторые преимущества перед производными таблицами:

CTE похожи на таблицы, созданные с помощью CREATE [TEMPORARY] TABLE, но не нуждаются в явном определении или удалении. Для CTE вам не требуется разрешение на создание таблиц.