MyTetra Share
Делитесь знаниями!
Рекурсивные SQL запросы / деревья
Время создания: 04.10.2017 11:02
Раздел: SQL Oracle - Иерархические запросы
Запись: xintrea/mytetra_db_mcold/master/base/15071041258dki4lju9y/text.html на raw.githubusercontent.com

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

Рекурсивны 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, spacelevel ) + nm as nm 
from tree 
order by pathstr



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

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

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

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

Так же в этом разделе:
 
MyTetra Share v.0.58
Яндекс индекс цитирования