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

Общие табличные выражения (CTE) — стандартная функция SQL и представляют собой, по сути, временные именованные наборы результатов. Существуют два типа CTE: нерекурсивные, которые рассматриваются в этой статье; и рекурсивные.

Нерекурсивные CTE

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

CTE похожи на производные таблицы. Например,

WITH engineers AS 
   ( SELECT * FROM employees
     WHERE dept = 'Engineering' )

SELECT * FROM engineers
WHERE ...

SELECT * FROM
   ( SELECT * FROM employees
     WHERE dept = 'Engineering' ) AS engineers
WHERE
...

Нерекурсивное CTE, по сути, является локальным запросом ВИДА. У них есть несколько преимуществ и ограничений. Синтаксис более удобочитаем, чем вложенные FROM (SELECT ...). CTE может ссылаться на другое CTE, и на него можно ссылаться из нескольких мест.

CTE, ссылающееся на другое CTE

Использование этого формата делает SQL более удобочитаемым, чем вложенный FROM(SELECT ...) оператор. Ниже приведен пример:

WITH engineers AS (
SELECT * FROM employees
WHERE dept IN('Development','Support') ),
eu_engineers AS ( SELECT * FROM engineers WHERE country IN('NL',...) )
SELECT
...
FROM eu_engineers;

Многократное использование CTE

Это может быть «анти-самосоединение», например:

WITH engineers AS (
SELECT * FROM employees
WHERE dept IN('Development','Support') )

SELECT * FROM engineers E1
WHERE NOT EXISTS
   (SELECT 1 FROM engineers E2
    WHERE E2.country=E1.country
    AND E2.name <> E1.name );

Или, для сравнения показателей с предыдущим годом, например:

WITH sales_product_year AS (
SELECT product, YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year )

SELECT *
FROM sales_product_year CUR,
sales_product_year PREV,
WHERE CUR.product=PREV.product 
AND  CUR.year=PREV.year + 1 
AND CUR.total_amt > PREV.total_amt

Другое применение — сравнение отдельных значений с группой. Ниже приведен пример того, как это можно выполнить:

WITH sales_product_year AS (
SELECT product,
YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year
)

SELECT * 
FROM sales_product_year S1
WHERE
total_amt > 
    (SELECT 0.1 * SUM(total_amt)
     FROM sales_product_year S2
     WHERE S2.year = S1.year)