SQL

Обобщенное табличное выражение в SQL. Рекурсия

Время прочтения: 3 мин.

Обобщенное табличное выражение (далее ОТВ) – это запрос с временными результатами, которые можно вызвать через операторы SELECT, UPDATE, INSERT, DELETE. Рассмотрим простой пример ниже:

/*Создаем ОТВ (назовем его "juice") и записываем в него 1 строчку в столбец "Соки"*/
WITH juice AS (SELECT 'Яблочный' [Соки])
/*Используем ОТВ в SELECT*/
SELECT [Соки] FROM juice

В результате выполнения данного скрипта вы получите:

Зачем использовать ОТВ?

  • ОТВ вы можете использовать для замены представлений, например, когда вам не требуется сохранять его в базе данных;
  • ОТВ упрощают написание и, главное, чтение кода. Например, если вам требуется несколько раз обращаться к «объединенным» таблицам одного формата, то удобнее один раз объединить их в ОТВ и обращаться непосредственно к ОТВ несколько раз через ключевое слово «FROM»;

Однако основной целью создания OTB является рекурсивные запросы. Об этом поговорим в следующем пункте.

Рекурсивный ОТВ

Рекурсивный ОТВ – это ОТВ, который ссылается на себя. При этом начальный ОТВ выполняется повторно, возвращая подмножества данных, пока не будет возвращен полный результат.

Возможность ссылаться на себя – уникальная особенность и преимущество. Это позволяет рекурсивным ОТВ решать проблемы запросов, которые в противном случае потребовали бы использования временных таблиц, курсоров и других средств.

Рекурсивные ОТВ хорошо подходят для запроса иерархических данных, таких как производственная ведомость материалов, где компоненты каждого продукта состоят из подкомпонентов и т. д. Ниже приведен рекурсивный ОТВ, который считает от 1 до 50:

WITH   recursion
AS     (SELECT 1 AS n – начальный элемент рекурсии
        UNION ALL
        SELECT n + 1 -- условие рекурсии
        FROM   recursion
        WHERE  n < 50 -- условие окончания рекурсии
       )
SELECT n
FROM   recursion -- выводим данные из ОТВ
OPTION (MAXRECURSION 200); -- ограничение рекурсии (по-умолчанию 100, если не указывать эту строку)

В результате выполнения данного скрипта вы получите 50 строк, от 1 до 50:

ОТВ пригодятся, когда вам нужно упростить запрос. Хотя некоторые утверждают, что использование рекурсивных ОТВ не способствует достижению этой цели, поскольку их концептуально сложно понять, однако ОТВ могут предоставить вам средства для идеального решения в определенных ситуациях. Есть много типов запросов, которые сложно решить без рекурсивных ОТВ. Запросы к иерархическим данным — одно из них. Конечно, вы можете писать циклические структуры для достижения той же цели, но если у вас нет средств для записи и выполнения хранимой процедуры, то рекурсивные ОТВ позволят вам сделать это, используя только оператор SELECT.

Подробнее на сайте microsoft.com.

Советуем почитать