С

Синтаксис

WITH [RECURSIVE] table_reference [(columns_list)] AS  (
  SELECT ...
)
[CYCLE cycle_column_list RESTRICT]
SELECT ...

Описание

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

Существует два типа CTE:

• Нерекурсивные
• Рекурсивные (обозначается ключевым словом RECURSIVE, поддерживается начиная с MariaDB 10.2.2)

Вы можете использовать table_reference как обычную таблицу во внешней части SELECT. Вы также можете использовать WITH в подзапросах, а также с EXPLAIN и SELECT.

Неправильно составленные рекурсивные CTE могут теоретически привести к бесконечным циклам. Системная переменная max_recursive_iterations ограничивает количество рекурсий.

CYCLE ... RESTRICT

WITH RECURSIVE ... (
  ...
)
CYCLE <cycle column list>
SET <cycle mark column> TO <cycle mark value> DEFAULT <non-cycle mark value>
USING <path column>

WITH RECURSIVE ... (
  ...
)
CYCLE <cycle column list> RESTRICT

Примеры

Ниже приведен пример с WITH на верхнем уровне:

WITH t AS (SELECT a FROM t1 WHERE b >= 'c') 
  SELECT * FROM t2, t WHERE t2.c = t.a;

В примере ниже используется WITH в подзапросе:

SELECT t1.a, t1.b FROM t1, t2
  WHERE t1.a > t2.c 
     AND t2.c IN(WITH t AS (SELECT * FROM t1 WHERE t1.a < 5)
                SELECT t2.c FROM t2, t WHERE t2.c = t.a);

Ниже приведен пример рекурсивного CTE:

WITH RECURSIVE ancestors AS 
 ( SELECT * FROM folks
   WHERE name="Alex"
   UNION
   SELECT f.*
   FROM folks AS f, ancestors AS a
   WHERE f.id = a.father OR f.id = a.mother )
SELECT * FROM ancestors;

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

CREATE TABLE t1 (from_ int, to_ int);
INSERT INTO t1 VALUES (1,2), (1,100), (2,3), (3,4), (4,1);
SELECT * FROM t1;
+-------+------+
| from_ | to_  |
+-------+------+
|     1 |    2 |
|     1 |  100 |
|     2 |    3 |
|     3 |    4 |
|     4 |    1 |
+-------+------+

В соответствии с вышеизложенным, следующий запрос теоретически приведет к бесконечному циклу из-за последней записи в t1 (обратите внимание, что max_recursive_iterations установлен в 10 для целей этого примера, чтобы избежать чрезмерного количества циклов):

SET max_recursive_iterations=10;

WITH RECURSIVE cte (depth, from_, to_) AS ( 
  SELECT 0,1,1 UNION DISTINCT SELECT depth+1, t1.from_, t1.to_ 
    FROM t1, cte  WHERE t1.from_ = cte.to_ 
) 
SELECT * FROM cte;
+-------+-------+------+
| depth | from_ | to_  |
+-------+-------+------+
|     0 |     1 |    1 |
|     1 |     1 |    2 |
|     1 |     1 |  100 |
|     2 |     2 |    3 |
|     3 |     3 |    4 |
|     4 |     4 |    1 |
|     5 |     1 |    2 |
|     5 |     1 |  100 |
|     6 |     2 |    3 |
|     7 |     3 |    4 |
|     8 |     4 |    1 |
|     9 |     1 |    2 |
|     9 |     1 |  100 |
|    10 |     2 |    3 |
+-------+-------+------+

Однако, оператор CYCLE ... RESTRICT (начиная с MariaDB 10.5.2) может решить эту проблему:

WITH RECURSIVE cte (depth, from_, to_) AS ( 
  SELECT 0,1,1 UNION SELECT depth+1, t1.from_, t1.to_ 
    FROM t1, cte WHERE t1.from_ = cte.to_ 
) 
CYCLE from_, to_ RESTRICT 
SELECT * FROM cte;
+-------+-------+------+
| depth | from_ | to_  |
+-------+-------+------+
|     0 |     1 |    1 |
|     1 |     1 |    2 |
|     1 |     1 |  100 |
|     2 |     2 |    3 |
|     3 |     3 |    4 |
|     4 |     4 |    1 |
+-------+-------+------+

См. также

• Обзор нерекурсивных общих выражений с таблицей
• Обзор рекурсивных общих выражений с таблицей