Новое в стандарте и реализациях языка SQL

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

Консоль

Выполнить

SELECT *, (SELECT MAX(price) FROM Laptop L2
JOIN  Product P1 ON L2.model=P1.model 
WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) max_price,
(SELECT MIN(price) FROM Laptop L2
JOIN  Product P1 ON L2.model=P1.model 
WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) min_price
FROM Laptop L1;

select *, (select MAX(price) from Laptop L2 join  Product P1 on L2.model=P1.model where maker = (select maker from Product P2 where P2.model= L1.model)) max_price, (select MIN(price) from Laptop L2 join  Product P1 on L2.model=P1.model where maker = (select maker from Product P2 where P2.model= L1.model)) min_price from Laptop L1;

    Схема

А при использовании CROSS APPLY мы просто добавим в подзапрос требуемую агрегатную функцию:

Консоль

Выполнить

SELECT *
 FROM laptop L1
 CROSS APPLY
 (SELECT MAX(price) max_price, MIN(price) min_price  FROM Laptop L2
JOIN  Product P1 ON L2.model=P1.model 
WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) X;

select *  from laptop L1  cross apply  (select MAX(price) max_price, MIN(price) min_price  from Laptop L2 join  Product P1 on L2.model=P1.model where maker = (select maker from Product P2 where P2.model= L1.model)) X;

    Схема

Рассмотрим еще один пример.

Соединить каждую строку из таблицы Laptop со следующей строкой в порядке, заданном сортировкой (model, code).

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

Консоль

Выполнить

SELECT * FROM laptop L1
CROSS APPLY
(SELECT TOP 1 * FROM Laptop L2 
WHERE L1.model < L2.model OR (L1.model = L2.model AND L1.code < L2.code) 
ORDER BY model, code) X
ORDER BY L1.model;

select * from laptop L1 CROSS APPLY (select top 1 * from Laptop L2 where L1.model < L2.model or (L1.model = L2.model and L1.code < L2.code) order by model, code) X order by L1.model;

    Схема

Попробуйте решить эту задачу традиционными средствами, чтобы сравнить трудозатраты.

Оператор OUTER APPLY

Как показывают результаты предыдущего запроса, мы "потеряли" последнюю (шестую) строку из таблицы Laptop, поскольку ее не с чем соединять. Другими словами, CROSS APPLY ведет себя как внутренне соединение. Аналогом же внешнего (левого) соединения является оператор OUTER APPLY. Он отличается от CROSS APPLY только тем, что выводит все строки из левой таблицы, заменяя отсутствующие значения из правой таблицы NULL-значениями.

Замена CROSS APPLY на OUTER APPLY в предыдущем запросе иллюстрирует сказанное.

Консоль

Выполнить

SELECT * FROM laptop L1
OUTER APPLY
(SELECT TOP 1 * 
FROM Laptop L2 
WHERE L1.model < L2.model OR (L1.model = L2.model AND L1.code < L2.code) 
ORDER BY model, code) X
ORDER BY L1.model;

select * from laptop L1 OUTER APPLY (select top 1 * from Laptop L2 where L1.model < L2.model or (L1.model = L2.model and L1.code < L2.code) order by model, code) X order by L1.model;

    Схема

Еще одной популярной задачей является вывод по N строк из каждой группы. Примером может служить вывод 5 наиболее популярных товаров в каждой категории. Рассмотрим следующую задачу.

Вывести из таблицы Product по три модели с наименьшими номерами из каждой группы, характеризуемой типом продукции.

Дополним решения, предложенные на сайте sql-ex.ru, решением, использующим CROSS APPLY. Идея заключается в соединении уникальных типов (первый запрос) с запросом, выводящих по 3 модели модели каждого типа из первого запроса в соответствии с требуемой сортировкой.

Консоль

Выполнить

SELECT X.* FROM 
(SELECT DISTINCT type FROM product) Pr1 
CROSS APPLY 
(SELECT TOP 3 * FROM product Pr2 WHERE  Pr1.type=Pr2.type ORDER BY pr2.model) x;

select X.* from (select distinct type from product) Pr1 cross apply (select top 3 * from product Pr2 where  Pr1.type=Pr2.type order by pr2.model) x;

    Схема

Нет схемы