Рекурсивные SQL запросы

Common Table Expressions - обобщенные табличные выражения или CTE действуют как временные views, которые существуют только на время выполнения одного оператора SQL. Существует два типа распространенных табличных выражений: "ordinary" и "recursive". Обычные табличные выражения облегчают понимание запросов за счет выделения подзапросов из основного оператора SQL. Рекурсивные общие табличные выражения предоставляют возможность выполнять иерархические или рекурсивные запросы к деревьям и графам, возможность, которая иначе недоступна в языке SQL.

Структура рекурсивного CTE

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Элемент привязки определен

UNION ALL

CTE_query_definition –- Рекурсивный член определяется ссылкой cte_name.

)

-- Заявление с использованием CTE

SELECT *

FROM cte_name

Следующий запрос возвращает все целые числа от 1 до 1000000:

WITH RECURSIVE

cnt(x) AS (

VALUES(1)

UNION ALL

SELECT x+1

FROM cnt

WHERE x<1000000

)

SELECT x

FROM cnt;

WITH RECURSIVE cnt(x) AS ( - Это начало определения рекурсивной CTE с именем cnt и одной колонкой x.

VALUES(1) - Это первый элемент в CTE с числом 1. Это начальное значение для последовательности.

UNION ALL — это ключевое слово, которое соединяет результаты предыдущего шага с новыми результатами, которые будут получены из следующего запроса.

SELECT x+1 FROM cnt WHERE x<1000000 - это часть запроса, которая определяет, как генерировать следующие элементы последовательности. Она берет предыдущий элемент x из CTE, добавляет к нему 1 и продолжает этот процесс до тех пор, пока x меньше 1000000.

SELECT x FROM cnt; — это финальный запрос, который выводит значения из CTE cnt. В результате выполнения этого запроса будут выведены числа от 1 до 1000000.

Сначала выполняется начальный выбор и возвращает одну строку с одним столбцом "1" Эта одна строка добавляется в очередь. На шаге 2a эта одна строка извлекается из очереди и добавляется в "cnt". Затем запускается рекурсивный выбор в соответствии с шагом 2c, генерируя одну новую строку со значением "2" для добавления в очередь. В очереди все еще есть одна строка, поэтому шаг 2 повторяется. Строка "2" извлекается и добавляется в рекурсивную таблицу на шагах 2a и 2b. Затем строка, содержащая 2, используется так, как если бы она была полным содержимым рекурсивной таблицы, и снова запускается рекурсивный выбор, в результате чего в очередь добавляется строка со значением "3". Это повторяется 999999 раз, пока, наконец, на шаге 2a единственным значением в очереди не станет строка, содержащая 1000000. Эта строка извлекается и добавляется в рекурсивную таблицу. Но на этот раз предложение WHERE приводит к тому, что рекурсивный выбор не возвращает ни одной строки, поэтому очередь остается пустой и рекурсия останавливается.