Предложение SQL With

В SQL предложение With также известно как CTE. Это мощная функция, которая позволяет нам создавать временные наборы результатов внутри запроса. Одна из основных ролей CTE — упрощение сложных запросов до более мелких и многократно используемых подзапросов. Это помогает сделать код более читабельным и удобным в сопровождении в долгосрочной перспективе.

Присоединяйтесь к нам в этом руководстве, чтобы мы изучили работу общих табличных выражений с использованием предложения With и поддерживаемых функций.

Требования:

В демонстрационных целях мы будем использовать следующее:

1. MySQL версии 8.0 и выше
2. Пример базы данных Сакила

При выполнении данных требований мы можем перейти к более подробному изучению CTE и предложения With.

Предложение SQL With

Предложение With позволяет нам определить один или несколько временных наборов результатов, которые известны как общие табличные выражения.

Мы можем ссылаться на полученные CTE в основном запросе, как и на любую другую таблицу или набор результатов. Это играет решающую роль при создании модульных SQL-запросов.

Хотя синтаксис CTE может незначительно отличаться в зависимости от ваших требований, ниже показан базовый синтаксис CTE в SQL:

Мы начинаем с ключевого слова With, которое сообщает базе данных SQL, что мы хотим создать и использовать CTE.

Далее мы указываем имя CTE, которое позволит нам ссылаться на него в других запросах.

Мы также указываем необязательный список имен столбцов, если CTE включает псевдонимы столбцов.

Далее мы приступаем к определению запроса CTE. Он содержит все задачи или данные, которые выполняет CTE, заключенные в пару круглых скобок.

Наконец, мы указываем основной запрос, который ссылается на CTE.

Пример использования:

Один из лучших способов понять, как использовать и работать с CTE, — рассмотреть практический пример.

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

Взгляните на следующий показанный CTE.

Использование предложения SQL With для поиска 10 крупнейших клиентов с наибольшим количеством арендных плат:

В данном примере мы начинаем с определения нового CTE, используя ключевое слово With, за которым следует имя, которое мы хотим присвоить CTE. В этом случае мы называем это «Аренда клиентов».

Внутри тела CTE мы вычисляем количество арендных плат для каждого клиента, объединяя таблицы клиентов и арендных плат.

Наконец, в основном запросе мы выбираем все столбцы из CTE, упорядочиваем результаты на основе количества арендных плат (в порядке убывания) и ограничиваем вывод только 10 верхними строками.

Это позволяет нам выбирать клиентов с наибольшим количеством арендованных автомобилей, как показано в следующем выводе:

Рекурсивные CTE

В некоторых других случаях вы можете иметь дело с иерархическими структурами данных. Именно здесь в игру вступают рекурсивные CTE.

Возьмем, к примеру, случай, когда мы хотим перемещаться по иерархической организации или представлять древовидную структуру. Мы можем использовать ключевое слово With RECURSIVE для создания рекурсивного CTE.

Поскольку в базе данных Sakila нет иерархических данных, которые мы могли бы использовать для демонстрации рекурсивного CTE, давайте создадим базовый пример.

В данном случае у нас есть образец таблицы «отдел» с некоторыми случайными данными. Чтобы найти иерархическую структуру отделов, мы можем использовать рекурсивный CTE следующим образом:

В этом случае рекурсивный CTE начинается с отделов, имеющих NULL «parent_department_id» (корневые отделы), и рекурсивно извлекает дочерние отделы.

Заключение

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