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

добавлено: 20 июн 13
понравилось:0
просмотров: 4026
комментов: 2

теги:

Автор: Алексей Куренков

Тема рекурсии достаточно хорошо освещена в литературе, но, тем не менее, задача вывода «дерева» не средствами клиента, а 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


Материал взят с сайта автора.

Комментарии


  • Думаю что нужно либо в название статьи вставить слово "рекурсия", либо так же осветить в статье hierarchyid, что тоже подходит для "Отображение древовидных структур на T-SQL".

  • 11 декабря 2015, 14:47 Алексей Куренков

    Пока не забыл... что бы не потерять. Возникла задача считать сумму подчиненных. Выложу скрипт сюда.


    declare @percent_bonus money = 0.2
    declare @tree table
    (
    id int primary key,
    parent int,
    title varchar(100),
    salary money
    );
    insert @tree values
    (1,null,'Генеральный директор', 500),
    (2,1,'Финансовый директор', 400),
    (3,1,'Директор ИТ', 370),
    (4,2,'Главный бухгалтер', 360),
    (5,2,'Главный экономист', 350),
    (6,4,'Бухгалтер по ОС', 300),
    (7,4,'Бухгалтер по ЗП', 280),
    (8,7,'Кассир', 200),
    (18,7,'Кассир2', 200),
    (9,3,'Ведущий программист', 310),
    (10,9,'Программист 1', 280),
    (11,9,'Программист 2', 270),
    (12,9,'Программист 3', 260),
    (13,3,'Ведущий сетевой администратор', 320),
    (14,13,'сисадмин 1', 250),
    (15,13,'сисадмин 2', 240),
    (16,13,'сисадмин 3', 230),
    (17,13,'сисадмин 4', 220);

    with recurs as
    (
    select *,
    0 as lev,
    cast(id as varchar(max)) as list
    from @tree
    where parent is null

    union all

    select t.*,
    lev+1,
    c.list+';'+cast(t.id as varchar(max))
    from @tree t join recurs c
    on c.id = t.parent
    )
    select
    g.id,
    g.parent,
    g.lev,
    space(g.lev*10)+g.title as title,
    g.salary,
    g.list,
    count(d.id) count_slaves,
    sum(d.salary) as slave_salary
    from recurs g
    left join recurs d
    on 1=1
    and g.list != d.list
    and d.list like g.list+';%'
    group by
    g.id,
    g.parent,
    g.lev,
    g.title,
    g.salary,
    g.list
    order by g.list



Необходимо войти на сайт, чтобы оставлять комментарии