Поделиться через

  • Статья

Применение обобщенных табличных выражений

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

ОТВ предназначены для:

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

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

  • Группирования по столбцу, производного от скалярного подзапроса выборки или функции, которая недетерминирована или имеет внешний доступ.

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

Применение ОТВ позволяет значительно повысить читаемость и упростить работу со сложными запросами, разбив его на отдельные логические строительные блоки. Из них можно составлять более сложные промежуточные ОТВ для формирования конечного результирующего набора.

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

Структура ОТВ

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

Базовая структура синтаксиса ОТВ:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

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

Инструкция для обращения к ОТВ:

SELECT <column_list>

FROM expression_name;

Пример

В следующем примере показаны компоненты структуры ОТВ: имя выражения, список столбцов и запрос. ОТВ Sales_CTE содержит три столбца (SalesPersonID, SalesOrderID, OrderDate) и определяет общее число заказов на продажу в год для всех менеджеров по продажам.

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

Вот частичный результирующий набор:

SalesPersonID TotalSales  SalesYear

------------- ----------- -----------

274           4           2001

274           20          2002

274           14          2003

274           10          2004

275           56          2001

275           139         2002

275           169         2003