loading..
Русский    English
11:33
листать

Рекурсивные СТЕ стр. 1

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

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

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

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

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

Консоль
Выполнить
  1. SELECT CHAR(ASCII('A')+5*(a-1) + b-1) AS num
  2. FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
  3. UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
  4. ) x CROSS JOIN
  5.  (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
  6. UNION ALL SELECT 4 UNION ALL SELECT 5
  7. ) y
  8. WHERE 5*(a-1) + b <= 26
  9. ORDER BY 1;

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

Консоль
Выполнить
  1. ;WITH Letters AS(
  2. SELECT ASCII('A') code, CHAR(ASCII('A')) letter
  3. UNION ALL
  4. SELECT code+1, CHAR(code+1) FROM Letters
  5. WHERE code+1 <= ASCII('Z')
  6. )
  7. SELECT letter FROM Letters;

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

Оператор

  1. SELECT letter FROM Letters
собственно и служит для обращения к CTE, запуска рекурсии и вывода результата. Все остальное можно считать определением.

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

  1. OPTION(MAXRECURSION N)
где N – максимальное число итераций. Значение 0 не ограничивает число итераций. Нужно с осторожностью использовать это значение, т.к. оно чревато зацикливанием.

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

The statement terminated. The maximum recursion N has been exhausted before statement completion.  (Выполнение оператора прервано. Достигнут предел максимального числа итераций N до завершения выполнения оператора).

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

Bookmark and Share
Страницы: 1 2 3
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
вчера
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100