Клауза SQL WITH

В SQL клаузата WITH е известна също като CTE. Това е мощна функция, която ни позволява да създаваме временни набори от резултати в рамките на заявка. Една основна роля на CTE е опростяването на сложните заявки в по-малки и повторно използвани подзаявки. Това помага да направите кода по-четлив и поддържаем в дългосрочен план.

Присъединете се към нас в този урок, докато изследваме работата на общите таблични изрази, използвайки клаузата WITH и поддържаната функционалност.

Изисквания:

За демонстрационни цели ще използваме следното:

1. MySQL версия 8.0 и по-нова
2. Sakila примерна база данни

След като дадените изисквания са изпълнени, можем да продължим да научаваме повече за 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. В този случай ние го наричаме „CustomerRentals“.

Вътре в тялото на CTE изчисляваме броя на наемите за всеки клиент, като съединяваме таблицата с клиенти и наеми.

И накрая, в основната заявка избираме всички колони от CTE, подреждаме резултатите въз основа на броя на наемите (низходящ ред) и ограничаваме изхода само до първите 10 реда.

Това ни позволява да извлечем клиентите с най-голям брой наеми, както е показано в следния резултат:

Рекурсивни CTE

В някои други случаи може да имате работа с йерархични структури от данни. Тук влизат в действие рекурсивните CTE.

Нека вземем за пример случай, в който искаме да навигираме в йерархичната организация или да представим дървовидна структура. Можем да използваме ключовата дума WITH RECURSIVE, за да създадем рекурсивен CTE.

Тъй като няма йерархични данни, които можем да използваме в базата данни Sakila, за да демонстрираме рекурсивен CTE, нека създадем основен пример.

В този случай имаме примерна таблица „отдел“ с някои произволни данни. За да намерим йерархичната структура на отделите, можем да използваме рекурсивен CTE, както следва:

В този случай рекурсивният CTE започва с отдели, които имат NULL „parent_department_id“ (основни отдели) и рекурсивно извлича дъщерните отдели.

Заключение

В този урок научихме за най-фундаменталните и полезни функции в SQL базите данни като Common Table Expressions, като разбрахме как да работим с ключовата дума WITH.