Выражение WITH

Выражение WITH позволяет указать общие табличные выражения (CTE). Регулярные (нерекурсивные) общие табличные выражения — это, по сути, представления (view), которые ограничены рамками конкретного запроса. CTE могут ссылаться друг на друга и быть вложенными. Рекурсивные CTE могут ссылаться сами на себя.

Синтаксис

Подзапрос

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

Рекурсивное CTE:

[ WITH [ RECURSIVE ]
    <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause)
 [, <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause)]
 [, <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause)]
]
SELECT ...

где:

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Параметры

  • <cte_name1> , <cte_nameN>
    Имя CTE.

  • <cte_column_list>
    Имена столбцов в CTE (общее табличное выражение).

  • <anchor_column_list>
    Столбцы, используемые в анкерном выражении для рекурсивного CTE. Столбцы в этом списке должны соответствовать столбцам, определенным в <cte_column_list>.

  • <recursive_column_list>
    Столбцы, используемые в рекурсивном выражении для рекурсивного CTE. Столбцы в этом списке должны соответствовать столбцам, определенным в <cte_column_list>.

Примеры базовых CTE

Создадим CTE с именем cte и используем его в основном запросе:

WITH cte AS (SELECT 33 AS amount)
    SELECT * FROM cte;
+--------+
| amount |
+--------+
| 33     |
+--------+

Создадим два CTE cte1 и cte2, где второй CTE ссылается на первый CTE:

WITH
    cte1 AS (SELECT 33 AS i),
    cte2 AS (SELECT i * 2 AS amount_doubled FROM cte1)
SELECT * FROM cte2;
+----------------+
| amount_doubled |
+----------------+
| 66             |
+----------------+

Для столбцов CTE можно задавать имена:

WITH my_cte(amount) AS (SELECT 33 AS i)
SELECT * FROM my_cte;
+--------+
| amount |
+--------+
| 33     |
+--------+