Ms sql дерево одним запросом

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

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

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

В 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.

Источник

Отображение древовидных структур на T-SQL

Тема рекурсии достаточно хорошо освещена в литературе, но, тем не менее, задача вывода «дерева» не средствами клиента, а SQL Server многих ставит в тупик. Для себя я использую подобный запрос для вывода дерева блокировок (динамическое представление — sys.processes). Возможны и другие применения, надеюсь читатель статьи сам для себя определится для чего нужен и полезен этот запрос.

Итак, ставим задачу:

Есть таблица с именем, идентификатором записи и полем указывающим на идентификатор предка. Сразу заполним эту таблицу, какими-то тестовыми значениями.

declare @tree table ( id int primary key, parent int, title varchar(100) ); insert @tree values (1,null,'Генеральный директор'), (2,1,'Финансовый директор'), (3,1,'Директор ИТ'), (4,2,'Главный бухгалтер'), (5,2,'Главный экономист'), (6,4,'Бухгалтер по ОС'), (7,4,'Бухгалтер по ЗП'), (8,7,'Кассир'), (9,3,'Ведущий программист'), (10,9,'Программист 1'), (11,9,'Программист 2'), (12,9,'Программист 3'), (13,3,'Ведущий сетевой администратор'), (14,13,'сисадмин 1'), (15,13,'сисадмин 2'), (16,13,'сисадмин 3'), (17,13,'сисадмин 4'); 

Необходимо вывести имена в древовидной структуре запросом на T-SQL: Генеральный директор

Директор ИТ 
Ведущий программист 
Программист 1 
Программист 2 
Программист 3 
Ведущий сетевой администратор 
сисадмин 1 
сисадмин 2 
сисадмин 3 
сисадмин 4 
Финансовый директор 
Главный бухгалтер 
Бухгалтер по ЗП 
Кассир 
Бухгалтер по ОС 
Главный экономист

C MS-SQL 2005 появилась конструкция на T-SQL с названием «обобщенные табличные выражения (common table expression — CTE)», которая позволяет реализовывать линейную рекурсию (жаль конечно что нет каскадной рекурсии, но имея возможность линейной рекурсии мы уже можем со многими задачами справляться).

В общем, рекурсивный синтаксис CTE в том, что мы должны объявить «якорь» точку с которой старт рекурсии будет, и с помощью конструкции объединения «UNION ALL» добавить запрос который использует имя нашей CTE — запрос ссылается сам на себя, конечно не забываем второй запрос ограничивать условием выхода из рекурсии.

Пример генерации последовательности целых чисел рекурсивно выглядит примерно так:

with numbers (i) as ( select 1 union all select i+1 from numbers where i) select * from numbers

Где select 1 не что иное как «якорь», select i+1 from numbers — это рекурсивный запрос, и where i

Для решения нашей задачи, нам потребуется «глубина вложения — уровень (lev)», для того что бы понять на сколько делать отступ каждой строки (отступ будем делать с помощью функций или SPACE или REPLICATE — кому что больше нравится). И так же нам необходимо вычислить поле, по которому будем делать сортировку при выводе результата, что бы не получилось так что программист подчиняется главному бухгалтеру. Самое простое, что приходит «на ум» —это формировать текущую должность и весь список руководителей:

Генеральный директор
Генеральный директор ;Директор ИТ
Генеральный директор ;Директор ИТ ;Ведущий программист
Генеральный директор ;Директор ИТ ;Ведущий программист ;Программист 1
Генеральный директор ;Директор ИТ ;Ведущий программист ;Программист 2
Генеральный директор ;Директор ИТ ;Ведущий программист ;Программист 3
Генеральный директор ;Директор ИТ ;Ведущий сетевой администратор
Генеральный директор ;Директор ИТ ;Ведущий сетевой администратор ;сисадмин 1
Генеральный директор ;Директор ИТ ;Ведущий сетевой администратор ;сисадмин 2
Генеральный директор ;Директор ИТ ;Ведущий сетевой администратор ;сисадмин 3
Генеральный директор ;Директор ИТ ;Ведущий сетевой администратор ;сисадмин 4
Генеральный директор ;Финансовый директор
Генеральный директор ;Финансовый директор ;Главный бухгалтер
Генеральный директор ;Финансовый директор ;Главный бухгалтер ;Бухгалтер по ЗП
Генеральный директор ;Финансовый директор ;Главный бухгалтер ;Бухгалтер по ЗП ;Кассир
Генеральный директор ;Финансовый директор ;Главный бухгалтер ;Бухгалтер по ОС
Генеральный директор ;Финансовый директор ;Главный экономист

И уже сортировать по этому полю (поле назовем – «list»).

Теперь, когда мы понимаем, суть алгоритма, я представлю запрос который у меня получился:

declare @tree table ( id int primary key, parent int, title varchar(100) ); insert @tree values (1,null,'Генеральный директор'), (2,1,'Финансовый директор'), (3,1,'Директор ИТ'), (4,2,'Главный бухгалтер'), (5,2,'Главный экономист'), (6,4,'Бухгалтер по ОС'), (7,4,'Бухгалтер по ЗП'), (8,7,'Кассир'), (9,3,'Ведущий программист'), (10,9,'Программист 1'), (11,9,'Программист 2'), (12,9,'Программист 3'), (13,3,'Ведущий сетевой администратор'), (14,13,'сисадмин 1'), (15,13,'сисадмин 2'), (16,13,'сисадмин 3'), (17,13,'сисадмин 4'); with recurs as ( select *, 0 as lev, cast(title as varchar(max)) as list from @tree where parent is null union all select t.*, lev+1, c.list+' ;'+t.title from @tree t join recurs c on c.id = t.parent ) select id, parent, space(lev*10)+title as title, lev, list from recurs order by list

Источник

CTE Recursion to get tree hierarchy

I need to get an ordered hierarchy of a tree, in a specific way. The table in question looks a bit like this (all ID fields are uniqueidentifiers, I’ve simplified the data for sake of example):

EstimateItemID EstimateID ParentEstimateItemID ItemType -------------- ---------- -------------------- -------- 1 A NULL product 2 A 1 product 3 A 2 service 4 A NULL product 5 A 4 product 6 A 5 service 7 A 1 service 8 A 4 product

A ___/ \___ / \ 1 4 / \ / \ 2 7* 5 8 / / 3* 6*

Using this query, I can get the hierarchy (just pretend ‘A’ is a uniqueidentifier, I know it isn’t in real life):

DECLARE @EstimateID uniqueidentifier SELECT @EstimateID = 'A' ;WITH temp as( SELECT * FROM EstimateItem WHERE EstimateID = @EstimateID UNION ALL SELECT ei.* FROM EstimateItem ei INNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID ) SELECT * FROM temp 

EstimateItemID -------------- 1 2 3 4 5 6 7 8

Unfortunately, what I need is an ordered hierarchy with a result set that follows the following constraints:

1. each branch must be grouped 2. records with ItemType 'product' and parent are the top node 3. records with ItemType 'product' and non-NULL parent grouped after top node 4. records with ItemType 'service' are bottom node of a branch

EstimateItemID -------------- 1 2 3 7 4 5 8 6

3 Answers 3

;WITH items AS ( SELECT EstimateItemID, ItemType , 0 AS Level , CAST(EstimateItemID AS VARCHAR(255)) AS Path FROM EstimateItem WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID UNION ALL SELECT i.EstimateItemID, i.ItemType , Level + 1 , CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255)) FROM EstimateItem i INNER JOIN items itms ON itms.EstimateItemID = i.ParentEstimateItemID ) SELECT * FROM items ORDER BY Path 

With Path — rows a sorted by parents nodes

If you want sort childnodes by ItemType for each level, than you can play with Level and SUBSTRING of Path column.

Here SQLFiddle with sample of data

Brilliant. This is a few years old, but found it useful today. However, forgive for saying, I found the example provided in the original post hard for me to translate into a more common solution. So, I reposted your (great) idea using more common data, table name, and fields to make it easier for others to follow.

Using the code above in SQL server 2017 (and ssms17) I get the error «Invalid column name ‘level’ » for the second level used (Level + 1). Any idea how to fix it?

@Ali, you can convert Path column into HierarchyId before ordering. You need slightly modify the path so it can be converted. Example

This is an add-on to Fabio’s great idea from above. Like I said in my reply to his original post. I have re-posted his idea using more common data, table name, and fields to make it easier for others to follow.

Thank you Fabio! Great name by the way.

First some data to work with:

CREATE TABLE tblLocations (ID INT IDENTITY(1,1), Code VARCHAR(1), ParentID INT, Name VARCHAR(20)); INSERT INTO tblLocations (Code, ParentID, Name) VALUES ('A', NULL, 'West'), ('A', 1, 'WA'), ('A', 2, 'Seattle'), ('A', NULL, 'East'), ('A', 4, 'NY'), ('A', 5, 'New York'), ('A', 1, 'NV'), ('A', 7, 'Las Vegas'), ('A', 2, 'Vancouver'), ('A', 4, 'FL'), ('A', 5, 'Buffalo'), ('A', 1, 'CA'), ('A', 10, 'Miami'), ('A', 12, 'Los Angeles'), ('A', 7, 'Reno'), ('A', 12, 'San Francisco'), ('A', 10, 'Orlando'), ('A', 12, 'Sacramento'); 

-- Note: The 'Code' field isn't used, but you could add it to display more info. ;WITH MyCTE AS ( SELECT ID, Name, 0 AS TreeLevel, CAST(ID AS VARCHAR(255)) AS TreePath FROM tblLocations T1 WHERE ParentID IS NULL UNION ALL SELECT T2.ID, T2.Name, TreeLevel + 1, CAST(TreePath + '.' + CAST(T2.ID AS VARCHAR(255)) AS VARCHAR(255)) AS TreePath FROM tblLocations T2 INNER JOIN MyCTE itms ON itms.ID = T2.ParentID ) -- Note: The 'replicate' function is not needed. Added it to give a visual of the results. SELECT ID, Replicate('.', TreeLevel * 4)+Name 'Name', TreeLevel, TreePath FROM MyCTE ORDER BY TreePath; 

Источник