Новое в стандарте и реализациях языка SQL

Общие табличные выражения (CTE)

У  CTE (общее табличное выражение) позволяет в рамках запроса задать таблицу, на которую можно многократно ссылаться.CTE есть еще одно важное назначение. С его помощью можно написать рекурсивный запрос, т.е. запрос, который, написанный один раз, будет повторяться многократно пока истинно некоторое условие.

Рекурсивный CTE имеет следующий вид:

WITH <имя>[(<список столбцов>)]
AS(
< SELECT... > -- анкорная часть
UNION ALL -- рекурсивная часть
< SELECT...FROM <имя>… > 
WHERE <условие продолжения итераций>
)

От обычного CTE-запроса рекурсивный отличается только рекурсивной частью, которая вводится предложением UNION ALL. Обратите внимание, что в рекурсивной части присутствует ссылка на имя CTE, т.е. внутри CTE ссылается само на себя. Это, собственно, и есть рекурсия. Естественно, анкорная и рекурсивная части должны иметь одинаковый набор столбцов.

В MySQL рекурсивные CTE (как и обычные) появились в версии 8. Их синтаксис фактически отличается от синтаксиса SQL Server только одним словом RECURSIVE:

WITH RECURSIVE <имя>[(<список столбцов>)]
AS(... и так далее

Перейдем к примеру. Рассмотрим задачу получения алфавита, т.е. таблицы алфавитных символов - прописных латинских букв. Чтобы было с чем сравнивать, решим сначала эту задачу с помощью генерации числовой последовательности, которая рассматривалась в параграфе 8.1.

Консоль

Выполнить

SELECT CHAR(ASCII('A')+5*(a-1) + b-1) AS num 
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
) x CROSS JOIN
 (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 
UNION ALL SELECT 4 UNION ALL SELECT 5 
) y 
WHERE 5*(a-1) + b <= 26
ORDER BY 1;

SELECT CHAR(ASCII('A')+5*(a-1) + b-1) AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 ) x CROSS JOIN  (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 ) y WHERE 5*(a-1) + b <= 26 ORDER BY 1;

    Схема

Нет схемы

А вот решение с помощью рекурсивного CTE

Консоль

Выполнить

;WITH Letters AS(
SELECT ASCII('A') code, CHAR(ASCII('A')) letter
UNION ALL
SELECT code+1, CHAR(code+1) FROM Letters
WHERE code+1 <= ASCII('Z')
)
SELECT letter FROM Letters;

;WITH Letters AS( SELECT ASCII('A') code, CHAR(ASCII('A')) letter UNION ALL SELECT code+1, CHAR(code+1) FROM Letters WHERE code+1 <= ASCII('Z') ) SELECT letter FROM Letters;

    Схема

Нет схемы

В запросе анкорной части определяем ASCII-код первой буквы алфавита и соответствующий ему символ. В запросе рекурсивной части мы просто увеличиваем ASCII-код на единицу, обращаясь к CTE в предложении FROM. В результате к строке с первым символом будут последовательно добавляться (UNION ALL) строки со следующими буквами в порядке их ASCII-кодов. Итерации будут продолжаться до тех пор, пока условие code +1 <= ascii('Z') будет истинным, т.е. пока не будет добавлена буква "Z".

Оператор

SELECT letter FROM Letters

Следует заметить, что по умолчанию допускается 100 итераций. Это значит, что если условие прекращения итераций не выполнено ранее, то рекурсия будет остановлена после выполнения 100 итераций. Максимальное число итераций можно изменить с помощью «хинта»

OPTION(MAXRECURSION N)

Если запрос не был завершен в пределах указанного числа итераций, возникает ошибка (полученные к этому моменту строки возвращаются):

В MySQL наша задача будет иметь аналогичное решение:

WITH RECURSIVE Letters AS(
    SELECT ASCII('A') code, CHAR(ASCII('A')) letter
    UNION ALL
    SELECT code+1, CHAR(code+1) FROM Letters
    WHERE code+1 <= ASCII('Z')
    )
    SELECT letter FROM Letters;

Давайте решим еще одну задачу в качестве ответа на вопрос, который мне неоднократно встречался на просторах Интернет.

Содержание:

О генерации числовых последовательностей в SQL Server