А теперь представьте, что нам нужно, помимо максимальной цены, вывести минимальную, среднюю цены и т.д. Поскольку коррелирующий подзапрос в предложении SELECT должен возвращать только одно значение, в первом варианте решения нам придется фактически дублировать код для каждого агрегата:
А при использовании CROSS APPLY мы просто добавим в подзапрос требуемую агрегатную функцию:
Рассмотрим еще один пример.
Соединить каждую строку из таблицы Laptop со следующей строкой в порядке, заданном сортировкой (model, code).
Столбец code в сортировке используется для того, чтобы задать однозначный порядок для строк, имеющих одинаковые значения в столбце model . С помощью CROSS APPLY мы можем передать в подзапрос параметры текущей строки и выбрать первую строку из тех, которые идут ниже текущей в заданном сортировкой порядке. Итак,
Попробуйте решить эту задачу традиционными средствами, чтобы сравнить трудозатраты.
Оператор OUTER APPLY
Как показывают результаты предыдущего запроса, мы «потеряли» последнюю (шестую) строку из таблицы Laptop, поскольку ее не с чем соединять. Другими словами, CROSS APPLY ведет себя как внутренне соединение. Аналогом же внешнего (левого) соединения является оператор OUTER APPLY. Он отличается от CROSS APPLY только тем, что выводит все строки из левой таблицы, заменяя отсутствующие значения из правой таблицы NULL-значениями.
Замена CROSS APPLY на OUTER APPLY в предыдущем запросе иллюстрирует сказанное.
Еще одной популярной задачей является вывод по N строк из каждой группы. Примером может служить вывод 5 наиболее популярных товаров в каждой категории. Рассмотрим следующую задачу.
Вывести из таблицы Product по три модели с наименьшими номерами из каждой группы, характеризуемой типом продукции.
Сейчас давайте рассмотрим достаточно полезный, в некоторых случаях, оператор 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) вот с такими данными:
А также у нас есть функция (FT_GET_Sales), которая просто выводит список продаж по идентификатору товара.
Пример ее работы:
Пример использования CROSS APPLY с табличной функцией
Теперь допустим, нам необходимо получить продажи не только одного товара, но и других, для этого мы можем использовать оператор CROSS APPLY
Где как Вы понимаете, табличная функция FT_GET_Sales была вызвана для каждой строки таблицы Products.
Пример использования CROSS APPLY с подзапросом
Как уже было сказано выше, CROSS APPLY можно использовать и с подзапросом, для примера давайте допустим, что нам нужно получить последнюю дату продажи каждого товара.
Как видим, после CROSS APPLY у нас идет подзапрос.
Пример использования OUTER APPLY
Если Вы заметили, у нас в исходных данных есть позиция «Клавиатура», по которой не было продаж. И в случае возникновения необходимости получить последнюю дату продажи включая товары, по которым не было продаж, т.е. выводить NULL значения, чтобы мы видели, какие товары не продавались вообще, тип CROSS APPLY можно заменить на OUTER APPLY.
Пример:
Теперь видно, что некоторые товары продавались и последняя дата их продажи такая то, а некоторые товары не продавались, т.е. у них отсутствует дата продажи.
Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные возможности языка Transact-SQL.
пример
Применить будет использоваться, когда функция table value в правильном выражении.
создайте таблицу отдела для хранения информации о департаментах. Затем создайте таблицу Employee, в которой хранятся сведения о сотрудниках. Обратите внимание: каждый сотрудник принадлежит отделу, поэтому таблица Employee имеет ссылочную целостность с таблицей Департамента.
Первый запрос выбирает данные из таблицы Department и использует CROSS APPLY для оценки таблицы Employee для каждой записи таблицы Department. Второй запрос просто присоединяется к таблице Департамента с таблицей Employee, и все соответствующие записи создаются.
Если вы посмотрите на результаты, которые они произвели, это точно такой же результат; Чем он отличается от JOIN и как он помогает в написании более эффективных запросов.
Первый запрос в сценарии # 2 выбирает данные из таблицы Department и использует OUTER APPLY для оценки таблицы Employee для каждой записи таблицы Department. Для тех строк, для которых нет совпадений в таблице Employee, эти строки содержат значения NULL, как вы можете видеть в случае строк 5 и 6. Второй запрос просто использует LEFT OUTER JOIN между таблицей Департамента и таблицей Employee. Как и ожидалось, запрос возвращает все строки из таблицы Department; даже для тех строк, для которых нет совпадений в таблице Employee.
Несмотря на то, что вышеупомянутые два запроса возвращают одну и ту же информацию, план выполнения будет бит другим. Но стоить разумно, не будет большой разницы.
Теперь настало время посмотреть, где действительно нужен оператор APPLY. В сценарии №3 я создаю табличную функцию, которая принимает параметр DepartmentID как параметр и возвращает всех сотрудников, принадлежащих этому отделу. Следующий запрос выбирает данные из таблицы Department и использует CROSS APPLY для объединения с созданной нами функцией. Он передает идентификатор отдела для каждой строки из выражения внешней таблицы (в нашем случае таблица Департамента) и оценивает функцию для каждой строки, подобной коррелированному подзапросу. Следующий запрос использует OUTER APPLY вместо CROSS APPLY и, следовательно, в отличие от CROSS APPLY, который возвращает только коррелированные данные, OUTER APPLY также возвращает некоррелированные данные, помещая NULL в отсутствующие столбцы.