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

Раздел: Software / SQL @ 17.06.2008 | Ключевые слова: sql рекурсия sql запрос

Автор: Anthony
Источник: habrahabr

Рекурсивны SQL запросы являются одним из способов решения проблемы дерева и других проблем, требующих рекурсивную обработку. Они были добавлены в стандарт SQL 99. До этого они уже существовали в Oracle. Несмотря на то, что стандарт вышел так давно, реализации запоздали. Например, в MS SQL они появились только в 2005-ом сервере.

Рекурсивные запросы используют довольно редко, прежде всего, из-за их сложного и непонятного синтаксиса:

with [recursive] <имя_алиаса_запроса> [ (<список столбцов>) ]
as (<запрос>)
<основной запрос>

В MS SQL нет ключевого слова recursive, а в остальном все тоже самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL и во всех базах данных, которые поддерживают стандарт SQL 99.

Проще разобрать на примере. Предположим, есть таблица:

create table tree_sample (
  id integer not null primary key,
  id_parent integer foreign key references tree_sample (id),
  nm varchar(31) )

id – идентификатор
id_parent – ссылка на родитель
nm – название.

Для вывода дерева:

with recursive tree (nm, id, level, pathstr)
as (select nm, id, 0, cast(`` as text)
   from tree_sample
   where id_parent is null
union all
   select tree_sample.nm, tree_sample.id, t.level + 1, tree.pathstr + tree_sample.nm
   from tree_sample
     inner join tree on tree.id = tree_sample.id_parent)
select id, space( level ) + nm as nm
from tree
order by pathstr

Этот пример выведет дерево по таблице с отступами. Первый запрос из tree_sample этот запрос выдаст все корни дерева. Второй запрос соединяет между собой таблицу tree_sample и tree, которая определяется этим же запросом. Этот запрос дополняет таблицу узлами дерева.

Сначала выполняется первый запрос. Потом к его результатам добавляются результаты второго запроса, где данные таблица tree – это результат первого запроса. Затем снова выполняется второй запрос, но данные таблицы tree – это уже результат предыдущего выполнения второго запроса. И так далее. На самом деле база данных работает не совсем так, но результат будет таким же, как результат работы описанного алгоритма.

После этого данные этой таблицы можно использовать в основном запросе как обычно.

Хочу заметить, что я не говорю о применимости конкретно этого примера, а лишь пишу его для демонстрации возможностей рекурсивных запросов. Этот запрос реально будет работать достаточно медленно из-за order by.

Распечатать статью