SQL Server ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ

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

Введение в предложение SQL Server CROSS APPLY

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

В SQL Server функция с табличным значением — это определяемая пользователем функция, которая возвращает несколько строк в виде таблицы.

Предложение CROSS APPLY работает как предложение INNER JOIN. Но вместо объединения двух таблиц предложение CROSS APPLY объединяет таблицу с таблично-значимой функцией или коррелированным подзапросом.

Вот базовый синтаксис предложения CROSS APPLY:

SELECT
  select_list
FROM
  table1
  CROSS APPLY table_function(table1.column) AS alias;

В этом синтаксисе:

• table1 — основная таблица, к которой вы хотите присоединиться.
• table_function: это таблично-значимая функция, применяемая к каждой строке. В качестве альтернативы можно использовать коррелированный подзапрос.
• столбец: столбец из table1, который будет передан в качестве параметра в table_function.
• alias — это псевдоним для набора результатов, возвращаемого table_function.

Предложение CROSS APPLY применит table_function к каждой строке из table1. Если вы используете коррелированный подзапрос, предложение CROSS APPLY выполнит его для каждой строки из table1.

На практике следует использовать предложения CROSS APPLY, когда невозможно использовать предложения INNER JOIN.

Примеры предложений SQL Server CROSS APPLY

Давайте рассмотрим некоторые полезные варианты использования предложения CROSS APPLY.

Для демонстрации мы будем использовать таблицы production.categories и production.products из образца базы данных :

1) Использование предложения SQL Server CROSS APPLY для соединения таблицы с коррелированным подзапросом

В следующем примере предложение CROSS APPLY используется для объединения таблицы production.categories с коррелированным подзапросом для извлечения двух самых дорогих продуктов для каждой категории продуктов:

SELECT
  c.category_name,
  r.product_name,
  r.list_price
FROM
  production.categories c
  CROSS APPLY(
    SELECT
      TOP 2 *
    FROM
      production.products p
    WHERE
      p.category_id = c.category_id
    ORDER BY
      list_price DESC,
      product_name
  ) r
ORDER BY
  c.category_name,
  r.list_price DESC;

Выход:

category_name       | product_name                                   | list_price
--------------------+------------------------------------------------+---------
Children Bicycles   | Electra Straight 8 3i(20-inch) - Boy's - 2017 | 489.99
Children Bicycles   | Electra Townie 3i EQ(20-inch) - Boys' - 2017  | 489.99
Comfort Bicycles    | Electra Townie Go! 8i - 2017/2018              | 2599.99
Comfort Bicycles    | Electra Townie Balloon 7i EQ - 2018            | 899.99
Cruisers Bicycles   | Electra Townie Commute Go! - 2018              | 2999.99
Cruisers Bicycles   | Electra Townie Commute Go! Ladies' - 2018      | 2999.99
Cyclocross Bicycles | Trek Boone 7 Disc - 2018                       | 3999.99
Cyclocross Bicycles | Trek Boone 7 - 2017                            | 3499.99
Electric Bikes      | Trek Powerfly 8 FS Plus - 2017                 | 4999.99
Electric Bikes      | Trek Powerfly 7 FS - 2018                      | 4999.99
Mountain Bikes      | Trek Fuel EX 9.8 27.5 Plus - 2017              | 5299.99
Mountain Bikes      | Trek Remedy 9.8 - 2017                         | 5299.99
Road Bikes          | Trek Domane SLR 9 Disc - 2018                  | 11999.99
Road Bikes          | Trek Domane SLR 8 Disc - 2018                  | 7499.99
(14 rows)

Как это работает.

Для каждой строки из таблицы production.categories CROSS APPLY выполняет следующий коррелированный подзапрос для извлечения двух самых дорогих продуктов:

SELECT
  TOP 2 *
FROM
  production.products p
WHERE
  p.category_id = c.category_id
ORDER BY
  list_price DESC,
  product_name

2) Использование предложения CROSS APPLY для соединения таблицы с табличной функцией

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

CREATE FUNCTION GetTopProductsByCategory(@category_id INT)
RETURNS TABLE
AS
RETURN(
    SELECT TOP 2 *
    FROM production.products p
    WHERE p.category_id = @category_id 
    ORDER BY list_price DESC, product_name
);

Во-вторых, используйте предложение CROSS APPLY с табличной функцией GetTopProductsByCategory, чтобы получить два самых дорогих продукта в каждой категории:

SELECT
  c.category_name,
  r.product_name,
  r.list_price
FROM
  production.categories c
  CROSS APPLY GetTopProductsByCategory(c.category_id) r
ORDER BY
  c.category_name,
  r.list_price DESC;

Он возвращает тот же результат, что и запрос, использующий коррелированный подзапрос выше.

3) Использование предложения CROSS APPLY для обработки данных JSON

Сначала создайте таблицу с именем product_json для хранения данных о продуктах:

CREATE TABLE product_json(
    id INT IDENTITY PRIMARY KEY,
    info NVARCHAR(MAX)
);

В таблице product_json:

• id — это столбец первичного ключа с атрибутом идентификатора.
• info — это NVARCHAR(MAX), в котором будут храниться данные JSON.

Во-вторых, вставьте строки в таблицу product_json:

INSERT INTO product_json(info)
VALUES 
   ('{"Name": "Laptop", "Price": 999, "Category": "Electronics"}'),
   ('{"Name": "Headphones", "Price": 99, "Category": "Electronics"}'),
   ('{"Name": "Book", "Price": 15, "Category": "Books"}');

В-третьих, извлеките информацию из данных JSON с помощью предложения CROSS APPLY с функцией OPENJSON() :

SELECT
  p.id,
  j.*
FROM
  product_json p
  CROSS APPLY OPENJSON(p.info) WITH
 (
    Name NVARCHAR(100),
    Price DECIMAL(10, 2),
    Category NVARCHAR(100)
  ) AS j;

Выход:

id | Name       | Price  | Category
---+------------+--------+-------------
1  | Laptop     | 999.00 | Electronics
2  | Headphones | 99.00  | Electronics
3  | Book       | 15.00  | Books
(3 rows)

4) Использование предложения CROSS APPLY для удаления вложенной функции REPLACE()

Сначала создайте таблицу под названием companies, в которой будут храниться названия компаний:

CREATE TABLE companies(
   id INT IDENTITY PRIMARY KEY,
   name VARCHAR(255) NOT NULL
);

Во-вторых, вставьте строки в таблицу компаний:

INSERT INTO
  companies(name)
VALUES
 ('ABC Corporation'),
 ('XYZ Inc.'),
 ('JK Pte Ltd');

Предположим, вы хотите получить названия компаний без слов, например Corporation, Inc. и Pte Ltd. Для этого вы можете использовать несколько функций REPLACE().

В-третьих, извлеките названия компаний из таблицы компаний:

SELECT TRIM(REPLACE(REPLACE(REPLACE(name,'Corporation',''), 'Inc.',''),'Pte Ltd','')) company_name
FROM companies;

Выход:

company_name
------------
ABC
XYZ
JK
(3 rows)

Запрос работает как и ожидалось, но он довольно сложен. Чтобы исправить это, вы можете использовать предложение CROSS APPLY следующим образом:

SELECT TRIM(r3.name) company_name
FROM companies c
CROSS APPLY(SELECT REPLACE(c.name,'Corporation', '') name) AS r1 
CROSS APPLY(SELECT REPLACE(r1.name,'Inc.', '') name) AS r2
CROSS APPLY(SELECT REPLACE(r2.name,'Pte Ltd', '') name) AS r3;

В этом запросе мы используем ряд предложений CROSS APPLY для постепенной замены определенных слов(Corporation, Inc. и Pte Ltd) в названиях компаний.

Краткое содержание

• Используйте предложение CROSS APPLY для выполнения внутреннего соединения таблицы с табличной функцией или коррелированным подзапросом.