Отображение древовидных структур на 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<10
)
select * from numbers

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

Для решения нашей задачи, нам потребуется «глубина вложения — уровень (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

Блог Алексея Куренкова

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

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