Обобщенное табличное выражение

Материал из Энциклопедия о программировании
Перейти к: навигация, поиск

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

Основные характеристики CTE

  • Не является объектом БД как, например, виды или хранимые процедуры
  • Обращение к CTE возможно только после его создания

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

Синтаксис CTE

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ (column_name [ ,...n ] ) ]
    AS
    (CTE_query_definition)

Аргументы

expression_name

Действительный идентификатор обобщенного табличного выражения. Имя аргумента expression_name должно отличаться от имени всех других обобщенных табличных выражений, определенных в том же предложении WITH <общее_табличное_выражение>, но expression_name может совпадать с именем базовой таблицы или представления. Любая ссылка на аргумент expression_name в запросе использует обобщенное табличное выражение, но не базовый объект.

column_name

Задается имя столбца в обобщенном табличном выражении. Повторяющиеся имена в определении одного CTE-выражения не допускаются. Количество заданных имен столбцов должно совпадать с количеством столбцов в результирующем наборе CTE_query_definition. Список имен столбцов необязателен только в том случае, если всем результирующим столбцам в определении запроса присвоены уникальные имена.

CTE_query_definition

Задается инструкция SELECT, результирующий набор которой заполняет обобщенное табличное выражение. Инструкция SELECT для CTE_query_definition должна удовлетворять таким же требованиям, что и при создании представления, за исключением того, что CTE-выражение не может определять другое CTE-выражение.

Если определено несколько параметров CTE_query_definition, определения запроса должны быть соединены одним из следующих операторов работы с наборами: UNION ALL, UNION, EXCEPT или INTERSECT. Дополнительные сведения об использовании определений рекурсивных CTE-выражений запросов.

Примеры CTE

Сперва само определяем CTE в скобках относящихся к директиве AS а после пишем сам запрос к этому CTE.

-- Пример использования обобщенного табличного выражения (CTE)
-- Найти товары, НЕ являющиеся самыми дорогими в своей ценовой категории (Class)
USE AdventureWorks2012
GO
;WITH MyCTE
AS (
    SELECT
        RANK() OVER
        (
            PARTITION BY Class
            ORDER BY ListPrice DESC
        ) AS Flag
    ,Class
    ,Name
    ,ListPrice
    FROM AdventureWorks2012.Production.Product
    )
SELECT * FROM MyCTE WHERE Flag > 1;

Ссылки