Общие Табличные Выражения CTE ("WITH … AS … SELECT")

COMMENTSОператоры процедурного SQL (PSQL)

Общие табличные выражения CTE ("WITH … AS … SELECT")

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

Синтаксис

<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [   <rows-clause>
    | { [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <cte> [, <cte> ...]

<cte> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]

Table 1. Параметры CTE
Параметр	Описание
query-name	Имя табличного выражения.
column-name	Псевдоним (алиас) для столбца табличного выражения.

Примечания

Определение CTE может содержать любой правильный оператор SELECT, если он не содержит преамбулы “WITH…” (операторы WITH не могут быть вложенными);
CTE могут использовать друг друга, но ссылки не должны иметь циклов;
CTE могут быть использованы в любой части главного запроса или другого табличного выражения и сколько угодно раз;
Основной запрос может ссылаться на CTE несколько раз, но с разными алиасами;
CTE могут быть использованы в операторах INSERT, UPDATE и DELETE как подзапросы;
Если объявленное CTE не использовано, то будет выдано предупреждение “CTE cte is not used in query”. В более ранних версиях вместо предупреждения выдавалась ошибка;

CTE могут быть использованы и в PSQL в FOR циклах:

FOR
  WITH
    MY_RIVERS AS (
      SELECT *
      FROM RIVERS
      WHERE OWNER = 'me'
    )
  SELECT
    NAME,
    LENGTH
  FROM MY_RIVERS
  INTO :RNAME,
       :RLEN
DO
BEGIN
  ...
END

Примеры

Example 1. Запрос с использованием CTE

WITH
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.DEPARTMENT,
  DYB_2008.BUDGET BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPARTMENT D
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2008
    ON D.DEPT_NO = DYB_2008.DEPT_NO AND
       DYB_2008.FISCAL_YEAR = 2008
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2009
    ON D.DEPT_NO = DYB_2009.DEPT_NO AND
       DYB_2009.FISCAL_YEAR = 2009
WHERE EXISTS (SELECT *
              FROM PROJ_DEPT_BUDGET B
              WHERE D.DEPT_NO = B.DEPT_NO)

Общие Табличные Выражения CTE ("WITH …​ AS …​ SELECT")