Оператор CROSS APPLY в T-SQL

Сейчас давайте рассмотрим достаточно полезный, в некоторых случаях, оператор CROSS APPLY языка T-SQL, затронем немного теории и, конечно же, рассмотрим примеры использования.

Оператор CROSS APPLY в T-SQL

Мы с Вами оператор CROSS APPLY как в справочнике T-SQL, так и в материале про основы программирования на T-SQL не рассматривали. Честно говоря, и в официальной документации данный оператор описан кратко, а он действительно полезный, поэтому сегодня я предлагаю рассмотреть возможности CROSS APPLY, в том числе и те, которые не описаны в документации.

CROSS APPLY

CROSS APPLY – это тип оператора APPLY, который позволяет вызывать табличную функцию для каждой строки внешнего табличного выражения. Вместо табличной функции можно также использовать и подзапрос. Есть еще и другой тип OUTER APPLY, он в отличие от CROSS APPLY возвращает и строки, которые формируют результирующий набор и те, которые этого не делают, т.е. со значениями NULL в столбцах.

Примечание! Примеры ниже будут рассмотрены в MS SQL Server 2008 R2.

Примеры использования CROSS APPLY

Для начала давайте определимся с исходными данными, допустим, у нас есть таблица товаров (Products) и таблица продаж (Sales) вот с такими данными:

 
   --ProductName – Наименование товара
   SELECT * FROM Products
   --ProductID – Идентификатор товара
   --DT – Дата продажи
   SELECT * FROM Sales

Скриншот 1

А также у нас есть функция (FT_GET_Sales), которая просто выводит список продаж по идентификатору товара.

 
   CREATE FUNCTION dbo.FT_GET_Sales(@ProductID INT)
   RETURNS TABLE
   AS
   RETURN
   (
        SELECT S.ID, S.DT, P.ProductName FROM Sales S
        LEFT JOIN Products P ON S.ProductID = P.ID
        WHERE S.ProductID = @ProductID
   )

Пример ее работы:

 
   SELECT Sales.DT, Sales.ProductName 
   FROM dbo.FT_GET_Sales(3) AS Sales

Скриншот 2

Пример использования CROSS APPLY с табличной функцией

Теперь допустим, нам необходимо получить продажи не только одного товара, но и других, для этого мы можем использовать оператор CROSS APPLY

 
   SELECT P.ProductName, Sales.DT 
   FROM Products P
   CROSS APPLY dbo.FT_GET_Sales(P.ID) AS Sales

Скриншот 3

Где как Вы понимаете, табличная функция FT_GET_Sales была вызвана для каждой строки таблицы Products.

Пример использования CROSS APPLY с подзапросом

Как уже было сказано выше, CROSS APPLY можно использовать и с подзапросом, для примера давайте допустим, что нам нужно получить последнюю дату продажи каждого товара.

 
   SELECT Products.ProductName, Sales.DT FROM Products 
   CROSS APPLY  (
                         SELECT TOP 1 P.DT, P.ProductName 
                         FROM dbo.FT_GET_Sales(Products.ID) AS P
                         ORDER BY P.DT DESC
                        )AS Sales

Скриншот 4

Как видим, после CROSS APPLY у нас идет подзапрос.

Пример использования OUTER APPLY

Если Вы заметили, у нас в исходных данных есть позиция «Клавиатура», по которой не было продаж. И в случае возникновения необходимости получить последнюю дату продажи включая товары, по которым не было продаж, т.е. выводить NULL значения, чтобы мы видели, какие товары не продавались вообще, тип CROSS APPLY можно заменить на OUTER APPLY.

Пример:

 
   SELECT Products.ProductName, Sales.DT FROM Products 
   OUTER APPLY  (
                         SELECT TOP 1 P.DT, P.ProductName 
                         FROM dbo.FT_GET_Sales(Products.ID) AS P
                         ORDER BY P.DT DESC
                        )AS Sales

Скриншот 5

Теперь видно, что некоторые товары продавались и последняя дата их продажи такая то, а некоторые товары не продавались, т.е. у них отсутствует дата продажи.

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные возможности языка Transact-SQL.

На этом все, удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.