Создать иерархический путь (SQL Server)

У меня есть таблица classstructure:

create table classstructure (classstructureid int, classificationid varchar(25), parent int);

insert into classstructure(classstructureid, classificationid, parent) values(1001, 'FLEET', null);
insert into classstructure(classstructureid, classificationid, parent) values(1002, 'LIGHTDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1004, 'MEDIUMDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1022, 'ACTIVETRANSPORTATION', null);
insert into classstructure(classstructureid, classificationid, parent) values(1023, 'FACILITYWALKWAY', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1024, 'TRAIL', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1085, 'SIDEWALK', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1091, 'SDWRAMP', 1085);


CLASSSTRUCTUREID CLASSIFICATIONID              PARENT
---------------- ------------------------- ----------
            1001 FLEET                               
            1002 LIGHTDUTYVEHICLE                1001
            1004 MEDIUMDUTYVEHICLE               1001

            1022 ACTIVETRANSPORTATION                
            1023 FACILITYWALKWAY                 1022
            1024 TRAIL                           1022
            1085 SIDEWALK                        1022
            1091 SDWRAMP                         1085

Я хочу создать запрос, который свернет записи до путей иерархии:

HIERARCHYPATH
---------------------------
FLEET
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE 

ACTIVETRANSPORTATION
ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

Как это сделать с помощью SQL?


sql-server parent-child sql-server-2019 hierarchical-data
person User1973    schedule 10.06.2021    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Короткий ответ. Этого можно добиться с помощью рекурсивного CTE. Вот еще один ответ на StackOverflow, в котором я ответил на этот вопрос: Иерархия учетных записей Salesforce Accounts - SQL-сервер

Подробное описание. Ваша структура данных представляет собой классический способ хранения иерархических данных в базе данных. Рекурсивный CTE начнется в определенной точке и рекурсивно зациклится до тех пор, пока условие не перестанет быть истинным (все дочерние элементы будут найдены), отсюда и название; рекурсивный. Есть несколько заявлений об отказе от ответственности, таких как ограничение рекурсии (сколько раундов оно делает), хотя при необходимости это можно смягчить.

person Eli    schedule 10.06.2021

arrow_upward
1
arrow_downward

Это будет стандартный рекурсивный CTE. CTE отлично подходят для малых и средних иерархий, однако, если ваша иерархия БОЛЬШАЯ, существуют другие методы, использующие временные таблицы, которые более эффективны.

Примечание: столбец HierID является необязательным. Он просто предлагает правильную последовательность

Пример или dbFiddle

;with cteP as (
      Select [CLASSSTRUCTUREID]
            ,[Parent] 
            ,[CLASSIFICATIONID]
            ,[HIERARCHYPATH] = convert(varchar(500),[CLASSIFICATIONID])
            ,[HierID]        = convert(hierarchyid,concat('/',[CLASSSTRUCTUREID],'/'))
      From   classstructure 
      Where  [Parent] is null
      Union  All
      Select [CLASSSTRUCTUREID]  = r.[CLASSSTRUCTUREID]
            ,[Parent]            = r.[Parent] 
            ,[CLASSIFICATIONID]  = r.[CLASSIFICATIONID]
            ,[HIERARCHYPATH]     = convert(varchar(500),concat(p.[HIERARCHYPATH],' \ ',r.[CLASSIFICATIONID]))
            ,[HierID]            = convert(hierarchyid,concat(p.HierID.ToString(),r.[CLASSSTRUCTUREID],'/'))
      From   classstructure  r
      Join   cteP p on r.[Parent]  = p.[CLASSSTRUCTUREID])
Select [CLASSSTRUCTUREID]
      ,[Parent]
      ,[CLASSIFICATIONID]
      ,[HIERARCHYPATH]
 From cteP A
 Order by [HierID]

Результаты

введите здесь описание изображения

Лично мне нравится включать УРОВЕНЬ иерархии в окончательные результаты. В окончательный SELECT вы можете включить:

...
[Level]   = HierID.GetLevel()
...
person John Cappelletti    schedule 10.06.2021

arrow_upward
1
arrow_downward

Ну вот:

with CTE_Rec as
(
    select 
        cast(classificationid as varchar(500)) classificationid, 
        classstructureid 
    from classstructure 
    where parent is null
    
    union All
    
    Select 
        cast(b.classificationid+' \ '+a.classificationid as varchar(500)),
        a.classstructureid
    from classstructure a 
    inner join CTE_REC b on b.classstructureid = a.parent 
) 
Select 
    classificationid 
from CTE_Rec
order by classificationid
person PankajSanwal    schedule 10.06.2021