Обобщенные табличные выражения (ОТВ)

Обобщенные табличные выражения (ОТВ) можно представить себе как временные результирующие наборы, определенные в области выполнения единичных инструкций SELECT, INSERT, UPDATE, DELETE или CREATE VIEW. ОТВ, как и производные таблицы, не сохраняются в базе данных в виде объектов, время их жизни ограничено продолжительностью запроса. Но, в отличие от производных таблиц, ОТВ могут ссылаться сами на себя, а на них один и тот же запрос может ссылаться несколько раз.

В основном ОТВ предназначены для:

· Создания рекурсивных запросов.

· Замены представлений в тех случаях, когда нет необходимости сохранять в метаданных базы его определение.

· Многократных ссылок на результирующую таблицу из одной и той же инструкции.

ОТВ могут быть определены в пользовательских подпрограммах (функциях, хранимых процедурах, триггерах, представлениях).

Базовый синтаксиса ОТВ:

WITH имя_ОТВ [ (список_имен_столбцов) ]

AS

(определение_ОТВ)

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

В простейшем случае инструкция для обращения к ОТВ имеет вид:

SELECT {*|список_имен_столбцов} FROM имя_ОТВ;

Пример. Отобразить среднее количество поставок для поставщиков в учебной базе данных SPJ

WITH CTE (SupplierNo, NumberOfShips)

AS

(

SELECT Sno, COUNT(*) AS Total

FROM SPJ

WHERE Sno IS NOT NULL

GROUP BY Sno

)

SELECT AVG(NumberOfShips) AS "Среднее количество поставок для поставщиков"

FROM CTE;

GO