В SQL предложение With также известно как CTE. Это мощная функция, которая позволяет нам создавать временные наборы результатов внутри запроса. Одна из основных ролей CTE — упрощение сложных запросов до более мелких и многократно используемых подзапросов. Это помогает сделать код более читабельным и удобным в сопровождении в долгосрочной перспективе.
Присоединяйтесь к нам в этом руководстве, чтобы мы изучили работу общих табличных выражений с использованием предложения With и поддерживаемых функций.
Требования:
В демонстрационных целях мы будем использовать следующее:
- MySQL версии 8.0 и выше
- Пример базы данных Сакила
При выполнении данных требований мы можем перейти к более подробному изучению CTE и предложения With.
Предложение SQL With
Предложение With позволяет нам определить один или несколько временных наборов результатов, которые известны как общие табличные выражения.
Мы можем ссылаться на полученные CTE в основном запросе, как и на любую другую таблицу или набор результатов. Это играет решающую роль при создании модульных SQL-запросов.
Хотя синтаксис CTE может незначительно отличаться в зависимости от ваших требований, ниже показан базовый синтаксис CTE в SQL:
С именем_cte (столбец1, столбец2, ...) AS (
-- Запрос CTE
ВЫБИРАТЬ ...
ОТ ...
ГДЕ ...
)
-- Основной запрос
ВЫБИРАТЬ ...
ОТ ...
ПРИСОЕДИНЯЙТЕСЬ к cte_name ON ...
ГДЕ ...
Мы начинаем с ключевого слова With, которое сообщает базе данных SQL, что мы хотим создать и использовать CTE.
Далее мы указываем имя CTE, которое позволит нам ссылаться на него в других запросах.
Мы также указываем необязательный список имен столбцов, если CTE включает псевдонимы столбцов.
Далее мы приступаем к определению запроса CTE. Он содержит все задачи или данные, которые выполняет CTE, заключенные в пару круглых скобок.
Наконец, мы указываем основной запрос, который ссылается на CTE.
Пример использования:
Один из лучших способов понять, как использовать и работать с CTE, — рассмотреть практический пример.
Возьмем, к примеру, образец базы данных Sakila. Предположим, мы хотим найти 10 лучших клиентов с наибольшим количеством аренд.
Взгляните на следующий показанный CTE.
Использование предложения SQL With для поиска 10 крупнейших клиентов с наибольшим количеством арендных плат:
С CustomerRentals AS (ВЫБЕРИТЕ c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rent_count
ОТ клиента c
ПРИСОЕДИНЯЙТЕСЬ к аренде r ON c.customer_id = r.customer_id
ГРУППИРОВАТЬ ПО c.customer_id, c.first_name, c.last_name
)
ВЫБИРАТЬ *
ОТ клиентаАренда
ЗАКАЗАТЬ ПО rent_count DESC
ОГРАНИЧЕНИЕ 10;
В данном примере мы начинаем с определения нового CTE, используя ключевое слово With, за которым следует имя, которое мы хотим присвоить CTE. В этом случае мы называем это «Аренда клиентов».
Внутри тела CTE мы вычисляем количество арендных плат для каждого клиента, объединяя таблицы клиентов и арендных плат.
Наконец, в основном запросе мы выбираем все столбцы из CTE, упорядочиваем результаты на основе количества арендных плат (в порядке убывания) и ограничиваем вывод только 10 верхними строками.
Это позволяет нам выбирать клиентов с наибольшим количеством арендованных автомобилей, как показано в следующем выводе:
Рекурсивные CTE
В некоторых других случаях вы можете иметь дело с иерархическими структурами данных. Именно здесь в игру вступают рекурсивные CTE.
Возьмем, к примеру, случай, когда мы хотим перемещаться по иерархической организации или представлять древовидную структуру. Мы можем использовать ключевое слово With RECURSIVE для создания рекурсивного CTE.
Поскольку в базе данных Sakila нет иерархических данных, которые мы могли бы использовать для демонстрации рекурсивного CTE, давайте создадим базовый пример.
СОЗДАТЬ ТАБЛИЦУ (Department_id INT PRIMARY KEY AUTO_INCREMENT,
имя_отдела VARCHAR(255) НЕ НУЛЬ,
родительский_department_id INT,
ВНЕШНИЙ КЛЮЧ (parent_department_id) ССЫЛКИ отдел (department_id)
);
INSERT INTO отдел (department_name, родительский_department_id)
ЦЕННОСТИ
(«Корпоративный», NULL),
(«Финансы», 1),
(«HR», 1),
(«Бухгалтерский учет», 2),
«Рекрутинг», 3),
(«Заработная плата», 4);
В данном случае у нас есть образец таблицы «отдел» с некоторыми случайными данными. Чтобы найти иерархическую структуру отделов, мы можем использовать рекурсивный CTE следующим образом:
С РЕКУРСИВНОЙ Иерархией Отделов AS (ВЫБЕРИТЕ id_отдела, имя_отдела, id_родителя_департамента
ИЗ отдела
ГДЕ родительский_департамент_ид имеет значение NULL
СОЮЗ ВСЕХ
ВЫБЕРИТЕ d.department_id, d.department_name, d.parent_department_id
ИЗ отдела д
ПРИСОЕДИНЯЙТЕСЬ к DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
ВЫБИРАТЬ *
ИЗ Иерархии отделов;
В этом случае рекурсивный CTE начинается с отделов, имеющих NULL «parent_department_id» (корневые отделы), и рекурсивно извлекает дочерние отделы.
Заключение
В этом руководстве мы узнали о наиболее фундаментальных и полезных функциях баз данных SQL, таких как общие табличные выражения, поняв, как работать с ключевым словом With.