Применение обобщенных табличных выражений
Обобщенные табличные выражения (ОТВ) можно представить себе как временные результирующие наборы, определенные в области выполнения единичных инструкций 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, NumberOfOrders и MaxDate) и определяет общее число заказов на поставку и дату последнего заказа в таблице SalesOrderHeader для каждого из менеджеров по продажам. Выполняемая инструкция ссылается на ОТВ дважды: чтобы получить необходимые столбцы для менеджера по продажам и чтобы получить подробные сведения о его руководителе. И данные о менеджере по продажам, и данные о его руководителе возвращаются в одной строке.
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
Ниже приведен частичный результирующий набор:
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268 48 2004-06-01 273 NULL NULL
275 450 2004-06-01 268 48 2004-06-01
276 418 2004-06-01 268 48 2004-06-01
277 473 2004-06-01 268 48 2004-06-01
См. также
Другие ресурсы
WITH общее_табличное_выражение (Transact-SQL)
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
CREATE VIEW (Transact-SQL)