SQL-деревья (T-SQL hierarchical tree)

September 16, 2017

Условие: имеется таблица, которая хранит в себе иерархию, например, подразделений.

Задача: заполнить в таблице полное наименование подразделения (от родителей к текущей записи).

Для решения поставленной задачи, нам необходимо построить дерево подразделений от самого потомка к родителю и развернуть наименования от родителя к потомку. Поиск в сети выдает много результатов, но нам важно, чтобы наименования были развернуты от родителя к потомку. Основное решение я нашел тут: SQL queries to manage hierarchical or parent child. В той статье приводится несколько вариантов работы с иерархическими данными. Под нашу задачу подходит вариант: All Possible Parents In A Column.

Немного трансформировав его, получаем:

 with FullPathDepartment(id, parent_id, name, parents, branchname, branch_id) as
 ( 
   select FirtGeneration.department_id, FirtGeneration.parent_id, iif(FirtGeneration.name is null, FirtGeneration.short_name, FirtGeneration.name), CAST(iif(FirtGeneration.name is null, FirtGeneration.short_name, FirtGeneration.name) AS VARCHAR(MAX)), cast (tops.name as nvarchar(max)), tops.id
   from departments as FirtGeneration, @topId as tops
   where FirtGeneration.parent_id = tops.id

   union all

   select
   NextGeneration.department_id, Parents.id, iif(NextGeneration.name is null, NextGeneration.short_name, NextGeneration.name),
   CAST(CASE WHEN Parents.parents = ''
               THEN(CAST(iif(NextGeneration.name is null, NextGeneration.short_name, NextGeneration.name) AS VARCHAR(MAX)))
               ELSE(Parents.parents + ', ' + CAST(iif(NextGeneration.name is null, NextGeneration.short_name, NextGeneration.name) AS VARCHAR(MAX)))
        END AS VARCHAR(MAX)),
        Parents.branchname,
        Parents.branch_id
   from departments as NextGeneration
   inner join FullPathDepartment as Parents on NextGeneration.parent_id = Parents.id
 )
 update d1
 set d1.full_name = d2.parents
    from departments as d1
    INNER JOIN FullPathDepartment as d2 on d1.department_id = d2.id
 OPTION(MAXRECURSION 32767);

Таблица department:

CREATE TABLE departments (
    [department_id] int not null identity,
    [parent_id] int ,
    [name] varchar(255) not null,
    [short_name] varchar(255) not null,
    PRIMARY KEY( [department_id] ),
    CONSTRAINT FK_parent FOREIGN KEY (parent_id)     
            REFERENCES department  (department_id)     
);

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

 insert into @topId  select dpt.department_id, iif(dpt.name is null, dpt.short_name, dpt.name)
        from departments as dpt inner join departments as parent on dpt.parent_id = parent.department_id
        where parent.parent_id is null;

И дальше объединяем со всеми потомками.