На днях мне попался APPLY, точнее CROSS APPLY.
Прочитав документацию о том, как это работает, и статьи об этом, у меня возникли проблемы с пониманием, потому что я не мог найти простое объяснение.
Я постараюсь объяснить это как можно проще, чтобы вы могли сразу начать использовать его.
Как работает CROSS APPLY
Основная идея CROSS APPLY — позволить вам объединить два набора данных.
Если вы понимаете, как работает INNER JOIN, вы уже понимаете CROSS APPLY.
Единственное отличие состоит в том, что CROSS APPLY также позволяет объединять набор данных, в котором этот набор данных создается или зависит от каждой строки в первом наборе.
Таким образом, в основном это означает, что для обычного объединения вы, например, должны объединить две таблицы с общим ключом.
Я мог бы присоединить свою таблицу клиентов к моей таблице заказов, чтобы увидеть все заказы для конкретного клиента, например, так:
|
1
2
3
4
5
6
7
|
SELECT *FROM orders o JOIN customers c ON o.customerid = c.customeridWHERE c.companyname = 'Around the Horn' |
Обратите внимание, как соединение работает с ключом, который существует независимо в каждой таблице.
Мы могли бы переписать это так же, используя синтаксис CROSS APPLY вместо этого так:
|
01
02
03
04
05
06
07
08
09
10
|
SELECT *FROM orders o CROSS APPLY ( SELECT * FROM customers c WHERE o.customerid = c.customerid) AS cWHERE c.companyname = 'Around the Horn' |
Мы можем доказать, что эти наборы результатов в точности совпадают, используя EXCEPT, чтобы убедиться, что в одном наборе нет строк, которых нет в другом, а затем перевернуть его, например, так:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
|
SELECT *FROM orders o CROSS APPLY (SELECT * FROM customers c WHERE o.customerid = c.customerid) AS cWHERE c.companyname = 'Around the Horn'EXCEPTSELECT *FROM orders o JOIN customers c ON o.customerid = c.customeridWHERE c.companyname = 'Around the Horn' |
Просто повторите этот точный запрос, поменяв SQL над ИСКЛЮЧЕНИЕМ с SQL ниже и убедитесь, что он также не дал результатов. Если оба этих запроса не дают результатов, то вы знаете, что результаты каждого запроса совпадают, так как EXCEPT покажет все результаты, которые находятся в верхнем запросе, но не в нижнем.
Так когда же будет полезен CROSS APPLY?
Помните, как я сказал, что это может сделать больше, чем простое соединение? Объединения ограничиваются только объединением двух наборов данных, которые могут быть запросами независимо друг от друга.
Что если я скажу, дайте мне три самых последних заказа для каждого клиента?
Найдите минутку и подумайте, как бы вы написали этот запрос. Идите и попробуйте сделать это. Я буду ждать.
Есть несколько способов сделать это без использования CROSS APPLY, но ни один из них на самом деле не очень прост или работает очень хорошо.
Используя CROSS APPLY это просто:
|
01
02
03
04
05
06
07
08
09
10
11
12
|
SELECT *FROM customers c CROSS APPLY ( SELECT TOP 3 o.orderdate , o.shipcity FROM orders o WHERE o.customerid = c.customerIdorder by o.orderdate desc) as top3; |
Таким образом, CROSS APPLY полезен всякий раз, когда у вас есть данные, к которым вы хотели бы иметь возможность присоединиться, но вместо этого вы вынуждены выполнять какой-то подзапрос, потому что данные, к которым вы пытаетесь присоединиться, плохо сопоставляются с одним ключ.
Другой пример, в котором CROSS APPLY будет полезен, — это когда вы делаете подвыбор, который имеет более одного значения, которое вы хотели бы использовать в своем окончательном запросе.
Например, если вы выбираете из таблицы «Детали заказа» дополнительный элемент для сопоставления идентификаторов заказов, количество которых превышает 5, этот дополнительный элемент должен возвращать ровно один столбец, чтобы вы могли использовать его в предложении where. Если вы хотите использовать другие столбцы из дополнительного выбора, вам нужно будет сделать еще один дополнительный выбор для каждого из этих столбцов.
Если вы сначала попытаетесь переписать суб-выбор как JOIN и обнаружите, что вы не можете, вы сможете написать его как CROSS APPLY.
Как узнать, когда использовать CROSS APPLY
Не существует хорошего твердого правила, которое вы можете использовать, чтобы определить, когда вам следует использовать CROSS APPLY, но знание CROSS APPLY и того, как оно работает, может помочь вам, когда вы пытаетесь настроить запросы и у вас возникают трудности при их создании. , Это еще один вариант, который вы можете попробовать.
Вот несколько общих советов, когда вы можете использовать CROSS APPLY:
- Запрос, по которому результирующий набор, к которому вы хотите присоединиться, каким-то образом связан с данными в первом наборе. (Пример: один столбец в первой таблице указывает, сколько строк во 2-й таблице нужно получить)
- Запрос, в котором вы выполняете подзапрос, но вам нужно более одного значения из подзапроса
- Везде, где вы используете Common Table Expression (CTE), возможно, можно переписать как CROSS APPLY
- Запрос с большим набором данных, к которому он присоединяется, а затем отфильтровывает. Вы можете изменить его на CROSS APPLY, который выполняет фильтрацию в операторе CROSS APPLY.
- Каждый раз, когда вы пытаетесь объединиться с табличной функцией (для этого и был создан CROSS APPLY).
Ссылка: добавьте APPLY в пояс инструментов TSQL от нашего партнера JCG Джона Сонмеза в разделе « Создание комплекса простым» .
Статьи по Теме :