Табличные выражения

Работа с базами данных в .NET Framework --- SQL Server 2012 --- Табличные выражения

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

производные таблицы;
обобщенные табличные выражения.

Эти две формы табличных выражений рассматриваются в следующих подразделах.

Производные таблицы

Производная таблица (derived table) - это табличное выражение, входящее в предложение FROM запроса. Производные таблицы можно применять в тех случаях, когда использование псевдонимов столбцов не представляется возможным, поскольку транслятор SQL обрабатывает другое предложение до того, как псевдоним станет известным. В примере ниже показана попытка использовать псевдоним столбца в ситуации, когда другое предложение обрабатывается до того, как станет известным псевдоним:

USE SampleDb;

SELECT MONTH(EnterDate) as enter_month
    FROM Works_on
    GROUP BY enter_month;

Попытка выполнить этот запрос выдаст следующее сообщение об ошибке:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'enter_month'.

(Сообщение 207: уровень 16, состояние 1, строка 5 
Недопустимое имя столбца enter_month)

Причиной ошибки является то обстоятельство, что предложение GROUP BY обрабатывается до обработки соответствующего списка инструкции SELECT, и при обработке этой группы псевдоним столбца enter_month неизвестен.

Эту проблему можно решить, используя производную таблицу, содержащую предшествующий запрос (без предложения GROUP BY), поскольку предложение FROM исполняется перед предложением GROUP BY:

USE SampleDb;

SELECT enter_month
    FROM (
        SELECT MONTH(EnterDate) as enter_month
        FROM Works_on)
    AS m
    GROUP BY enter_month;

Результат выполнения этого запроса будет таким:

Пример использования табличного выражения

Обычно табличное выражение можно разместить в любом месте инструкции SELECT, где может появиться имя таблицы. (Результатом табличного выражения всегда является таблица или, в особых случаях, выражение.) В примере ниже показывается использование табличного выражения в списке выбора инструкции SELECT:

USE SampleDb;

SELECT w.Job AS 'Работа', (SELECT e.LastName
    FROM Employee e WHERE e.Id = w.EmpId) AS 'Фамилия'
        FROM Works_on w
        WHERE w.Job IN ('Аналитик', 'Менеджер')

Результат выполнения этого запроса:

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

Обобщенным табличным выражением (OTB) (Common Table Expression - сокращенно CTE) называется именованное табличное выражение, поддерживаемое языком Transact-SQL. Обобщенные табличные выражения используются в следующих двух типах запросов:

нерекурсивных;
рекурсивных.

Эти два типа запросов рассматриваются в следующих далее разделах.

OTB и нерекурсивные запросы

Нерекурсивную форму OTB можно использовать в качестве альтернативы производным таблицам и представлениям. Обычно OTB определяется посредством предложения WITH и дополнительного запроса, который ссылается на имя, используемое в предложении WITH. В языке Transact-SQL значение ключевого слова WITH неоднозначно. Чтобы избежать неопределенности, инструкцию, предшествующую оператору WITH, следует завершать точкой с запятой.

далее показано использование OTB в нерекурсивных запросах. В примере ниже используется стандартное решение (здесь используется тестовая база данных AdventureWorks2012 из исходников):

USE AdventureWorks2012;

SELECT SalesOrderID
    FROM Sales.SalesOrderHeader
    WHERE TotalDue > (SELECT AVG(TotalDue)
                      FROM Sales.SalesOrderHeader
                      WHERE YEAR(OrderDate) = '2005')
        AND Freight > (SELECT AVG(TotalDue)
                       FROM Sales.SalesOrderHeader
                       WHERE YEAR(OrderDate) = '2005')/2.5;

Запрос в этом примере выбирает заказы, чьи общие суммы налогов (TotalDue) большие, чем среднее значение по всем налогам, и плата за перевозку (Freight) которых больше чем 40% среднего значения налогов. Основным свойством этого запроса является его объемистость, поскольку вложенный запрос требуется писать дважды. Одним из возможных способов уменьшить объем конструкции запроса будет создать представление, содержащее вложенный запрос. Но это решение несколько сложно, поскольку требует создания представления, а потом его удаления после окончания выполнения запроса. Лучшим подходом будет создать OTB. В примере ниже показывается использование нерекурсивного OTB, которое сокращает определение запроса, приведенного выше:

USE AdventureWorks2012;

WITH price_calc(year_2005) AS
    (SELECT AVG(TotalDue)
        FROM Sales.SalesOrderHeader
        WHERE YEAR(OrderDate) = '2005')
    SELECT SalesOrderID
        FROM Sales.SalesOrderHeader
        WHERE TotalDue > (SELECT year_2005 FROM price_calc)
            AND Freight > (SELECT year_2005 FROM price_calc)/2.5;

Синтаксис предложения WITH в нерекурсивных запросах имеет следующий вид:

WITH cte_name (column_list) AS
    (inner_query)
    outer_query


Соглашения по синтаксису

Параметр cte_name представляет имя OTB, которое определяет результирующую таблицу, а параметр column_list - список столбцов табличного выражения. (В примере выше OTB называется price_calc и имеет один столбец - year_2005.) Параметр inner_query представляет инструкцию SELECT, которая определяет результирующий набор соответствующего табличного выражения. После этого определенное табличное выражение можно использовать во внешнем запросе outer_query. (Внешний запрос в примере выше использует OTB price_calc и ее столбец year_2005, чтобы упростить употребляющийся дважды вложенный запрос.)

OTB и рекурсивные запросы

В этом разделе представляется материал повышенной сложности. Поэтому при первом его чтении рекомендуется его пропустить и вернуться к нему позже. Посредством OTB можно реализовывать рекурсии, поскольку OTB могут содержать ссылки на самих себя. Основной синтаксис OTB для рекурсивного запроса выглядит таким образом:

WITH cte_name (column_list) AS
    (anchor_member
     UNION ALL
     recursive_member)
     outer_query


Соглашения по синтаксису

Параметры cte_name и column_list имеют такое же значение, как и в OTB для нерекурсивных запросов. Тело предложения WITH состоит из двух запросов, объединенных оператором UNION ALL. Первый запрос вызывается только один раз, и он начинает накапливать результат рекурсии. Первый операнд оператора UNION ALL не ссылается на OTB. Этот запрос называется опорным запросом или источником.

Второй запрос содержит ссылку на OTB и представляет ее рекурсивную часть. Вследствие этого он называется рекурсивным членом. В первом вызове рекурсивной части ссылка на OTB представляет результат опорного запроса. Рекурсивный член использует результат первого вызова запроса. После этого система снова вызывает рекурсивную часть. Вызов рекурсивного члена прекращается, когда предыдущий его вызов возвращает пустой результирующий набор.

Оператор UNION ALL соединяет накопившиеся на данный момент строки, а также дополнительные строки, добавленные текущим вызовом рекурсивного члена. (Наличие оператора UNION ALL означает, что повторяющиеся строки не будут удалены из результата.)

Наконец, параметр outer_query определяет внешний запрос, который использует OTB для получения всех вызовов объединения обеих членов.

Для демонстрации рекурсивной формы OTB мы используем таблицу Airplane, определенную и заполненную кодом, показанным в примере ниже:

USE SampleDb;

CREATE TABLE Airplane (
    ContainingAssembly VARCHAR(10),
    ContainedAssembly VARCHAR(10),
    QuantityContained INT,
    UnitCost DECIMAL (6,2)
);

INSERT INTO Airplane VALUES ( 'Самолет', 'Фюзеляж',1, 10);
INSERT INTO Airplane VALUES ( 'Самолет', 'Крылья', 1, 11);
INSERT INTO Airplane VALUES ( 'Самолет', 'Хвост',1, 12);
INSERT INTO Airplane VALUES ( 'Фюзеляж', 'Салон', 1, 13);
INSERT INTO Airplane VALUES ( 'Фюзеляж', 'Кабина', 1, 14);
INSERT INTO Airplane VALUES ( 'Фюзеляж', 'Нос',1, 15);
INSERT INTO Airplane VALUES ( 'Салон', NULL, 1,13);
INSERT INTO Airplane VALUES ( 'Кабина', NULL, 1, 14);
INSERT INTO Airplane VALUES ( 'Нос', NULL, 1, 15);
INSERT INTO Airplane VALUES ( 'Крылья', NULL,2, 11);
INSERT INTO Airplane VALUES ( 'Хвост', NULL, 1, 12);

Таблица Airplane состоит из четырех столбцов. Столбец ContainingAssembly определяет сборку, а столбец ContainedAssembly - части (одна за другой), которые составляют соответствующую сборку. На рисунке ниже приведена графическая иллюстрация возможного вида самолета и его составляющих частей:

Таблица Airplane состоит из следующих 11 строк:

В примере ниже показано применение предложения WITH для определения запроса, который вычисляет общую стоимость каждой сборки:

USE SampleDb;

WITH list_of_parts(assembly1, quantity, cost) AS
    (SELECT ContainingAssembly, QuantityContained, UnitCost
     FROM Airplane
     WHERE ContainedAssembly IS NULL
     UNION ALL
        SELECT a.ContainingAssembly, a.QuantityContained,
            CAST(l.quantity * l.cost AS DECIMAL(6,2))
        FROM list_of_parts l, Airplane a
        WHERE l.assembly1 = a.ContainedAssembly)
SELECT assembly1 'Деталь', quantity 'Кол-во', cost 'Цена'
FROM list_of_parts;

Предложение WITH определяет список OTB с именем list_of_parts, состоящий из трех столбцов: assembly1, quantity и cost. Первая инструкция SELECT в примере вызывается только один раз, чтобы сохранить результаты первого шага процесса рекурсии. Инструкция SELECT в последней строке примера отображает следующий результат:

Первые пять строчек этого результата являются результирующим набором первого вызова опорного члена запроса, а все остальные строчки - результатом рекурсивного члена (вторая часть) запроса в этом примере. Рекурсивный член запроса вызывается дважды: первый раз для сборки фюзеляжа (fuselage), а второй раз для всего самолета (Airplane).

Запрос в примере ниже вычисляет стоимость каждой сборки со всеми ее составляющими:

USE SampleDb;

WITH list_of_parts(assembly1, quantity, cost) AS
    (SELECT ContainingAssembly, QuantityContained, UnitCost
     FROM Airplane
     WHERE ContainedAssembly IS NULL
     UNION ALL
        SELECT a.ContainingAssembly, a.QuantityContained,
            CAST(l.quantity * l.cost AS DECIMAL(6,2))
        FROM list_of_parts l, Airplane a
        WHERE l.assembly1 = a.ContainedAssembly)
SELECT assembly1 'Деталь', SUM(quantity) 'Кол-во частей', SUM(cost) 'Цена'
    FROM list_of_parts
    GROUP BY assembly1;

Результат выполнения этого запроса дает следующий результат:

В рекурсивных запросах на OTB налагается несколько следующих ограничений:

определение OTB должно содержать, по крайней мере, две инструкции SELECT (опорный член и рекурсивный член), объединенные оператором UNION ALL;
опорный член и рекурсивный член должны иметь одинаковое количество столбцов (это является прямым следствием использования оператора UNION ALL);
столбцы в рекурсивном члене должны иметь такой же тип данных, как и соответствующие столбцы в опорном члене;
предложение FROM рекурсивного члена должно ссылаться на имя OTB только один раз;
определение рекурсивного члена не может содержать следующие параметры: SELECT DISTINCT, GROUP BY, HAVING, агрегатные функции, TOP и подзапросы. Кроме этого, единственным типом операции соединения, разрешенной в определении запроса, является внутреннее соединение.