ReSQL.ru
     
powered by simpleCommunicator - 2.0.36     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Запрос на выборку иерархии родителей
3 сообщений из 3, страница 1 из 1
Запрос на выборку иерархии родителей
    #40139170
AlenaAAAA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlenaAAAA
Участник
Доброго времени суток, коллеги. Прошу вашей помощи, бьюсь весь день, все решения, приходящие в голову отрабатывают неверно.

Суть: есть иерархия отделов (будем называть все это "отделами"). Всего 3 уровня вложенности, есть главный "отдел", у него есть дочерние отделы, а у дочерних отделов есть еще дочерние отделы. Пример цепочки иерархии: 1. Административный департамент => 1.6. Управление административно-хозяйственной деятельности => 1.6.1. Отдел ИТ

Структура БД создана таким образом, что внесены ID отдела и ID отдела-родителя для каждой записи. Нужно брать ID отдела-родителя и искать его родителя выше. Единственное адекватное решение в моей голове - рекурсия и ОТВ, но запросы отрабатывают некорректно, либо после 2 уровня некорректно выводят 3 уровень, либо уже на 2 уровне не хотят видеть родителей правильно. Помогите с решением. В итоге необходимо отобразить иерархию в любом виде: либо в столбцах (3 столбца-уровня), либо объединить concat`ом, не суть.

Неуспешные попытки:
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
WITH DepartmentHierarchy AS (

    SELECT
        d.Id,
        dn.Name AS DepartmentName,
        d.IdParentDepartment,
        1 AS Level,
        CAST(dn.Name AS NVARCHAR(MAX)) AS FullDepartmentName,
        d.IdDepartmentName
    FROM dbo.Departments d
    INNER JOIN dbo.DepartmentName dn ON d.IdDepartmentName = dn.Id
    WHERE d.IdParentDepartment IS NULL -- Корневые департаменты

    UNION ALL

    SELECT
        d.Id,
        dn.Name AS DepartmentName,
        d.IdParentDepartment,
        dh.Level + 1 AS Level,
        CAST(dh.FullDepartmentName + ' > ' + dn.Name AS NVARCHAR(MAX)) AS FullDepartmentName,
        d.IdDepartmentName
    FROM dbo.Departments d
    INNER JOIN dbo.DepartmentName dn ON d.IdDepartmentName = dn.Id
    INNER JOIN DepartmentHierarchy dh ON d.IdParentDepartment = dh.Id
)

SELECT
    dh.FullDepartmentName AS DepartmentHierarchy,
    dh.Level
FROM DepartmentHierarchy dh
ORDER BY dh.Level, dh.FullDepartmentName;
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT
    d.Id AS DepartmentId,
    d1.Name AS Level1,        -- Первый уровень (корневой отдел)
    d2.Name AS Level2,        -- Второй уровень (подразделение корневого отдела)
    d3.Name AS Level3         -- Третий уровень (дочернее подразделение)
FROM Departments d
-- Первый уровень: текущий отдел
INNER JOIN DepartmentName d1 ON d1.Id = d.IdDepartmentName
-- Второй уровень: родитель текущего отдела
LEFT JOIN Departments dp ON dp.Id = d.IdParentDepartment
LEFT JOIN DepartmentName d2 ON d2.Id = dp.IdDepartmentName
-- Третий уровень: родитель второго уровня
LEFT JOIN Departments gdp ON gdp.Id = dp.IdParentDepartment
LEFT JOIN DepartmentName d3 ON d3.Id = gdp.IdDepartmentName
ORDER BY Level1, Level2, Level3;
Структура таблиц:
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE DepartmentName
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name nvarchar(180) NOT NULL
);

CREATE TABLE Departments
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    IdDepartmentName INT NOT NULL,
    IdParentDepartment INT,
    FOREIGN KEY (IdDepartmentName) REFERENCES DepartmentName(Id),
    FOREIGN KEY (IdParentDepartment) REFERENCES DepartmentName(Id)
);
Пример иерархии:

1. Административный департамент
1.1. Административный департамент
1.2. Договорной отдел
1.3. Общий отдел
1.4. Отдел закупок
1.5. Отдел протокольного сопровождения
1.6. Управление административно-хозяйственной деятельности
1.6.1. Отдел ИТ
1.6.2. Управление административно-хозяйственной деятельности
1.7. Управление безопасности
1.8. Управление по обеспечению безопасности
2. Академия Умные дороги
2.1. Академия Умные дороги
2.2. Отдел сетевых программ
2.3. Проектно-аналитический отдел
2.4. Учебно-организационный отдел
3. Аппарат управления
4. Департамент коммуникаций
4.1. Департамент коммуникаций
4.2. Управление по PR-проектам
4.2.1. Отдел по организации и сопровождению мероприятий
4.2.2. Отдел по работе с корпорациями
4.3. Управление Пресс-службы
4.3.1. Отдел по работе со СМИ
4.3.2. Отдел цифровых коммуникаций
4.3.3. Управление Пресс-службы
5. Департамент маркетинга и партнерских отношений
5.1. Департамент маркетинга и партнерских отношений
5.2. Управление маркетинга
5.2.1. Лицензионный отдел
5.2.2. Управление маркетинга
5.3. Управление по развитию бизнеса
5.3.1. Отдел по привлечению новых клиентов
5.3.2. Отдел по организации мероприятий
5.4. Управление по развитию партнерских отношений
6. Департамент по организации корпоративов
7. Департамент по работе с персоналом
8. Департамент по работе с промышленностью
9. Департамент стратегии и планирования
9.1. Аналитический отдел
9.2. Отдел проектного управления
10. Управление Финансового планирования и контроля
11. Финансово-экономический департамент
11.1. Управление бухгалтерского и налогового учета
11.2. Управление казначейства
11.2.1. Операционный отдел
11.3. Финансово-экономический департамент
11.4. Финансово-экономическое управление
12. Юридический департамент
12.1. Управление нормативного обеспечения и договорной работы
12.2. Юридический департамент
Screenshot_1.png
...
Изменено: 04.01.2025, 14:31 - AlenaAAAA
Рейтинг: 0 / 0
04.01.2025, 14:30
| Ответить | Цитировать | Написать | Ответы  
Запрос на выборку иерархии родителей
    #40139197
Фотография SeaGate
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SeaGate
Участник
AlenaAAAA [игнорируется] 

Входные/выходные данные нужны не в списке, а в виде, который можно вставить в редактор.
См пример ниже:
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
with DepartmentName(Id, Name) as (
  select 1, N'Административный департамент' union all
  select 2, N'Административный департамент' union all
  select 3, N'Договорной отдел' union all
  select 4, N'Общий отдел' union all
  select 5, N'Отдел закупок' union all
  select 6, N'Отдел протокольного сопровождения' union all
  select 7, N'Управление административно-хозяйственной деятельности' union all
  select 8, N'Отдел ИТ' union all
  select 9, N'Управление административно-хозяйственной деятельности' union all
  select 10, N'Управление безопасности' union all
  select 11, N'Управление по обеспечению безопасности' union all
  select 12, N'Академия Умные дороги' union all
  select 13, N'Академия Умные дороги' union all
  select 14, N'Отдел сетевых программ'),
  Departments(Id, IdDepartmentName, IdParentDepartment) as (
  select 1, 1, null union all
  select 2, 2, 1 union all
  select 3, 3, 1 union all
  select 4, 4, 1 union all
  select 5, 5, 1 union all
  select 6, 6, 1 union all
  select 7, 7, 1 union all
  select 8, 8, 7 union all
  select 9, 9, 7 union all
  select 10, 10, 1 union all
  select 11, 11, 1 union all
  select 12, 12, null union all
  select 13, 13, 12 union all
  select 14, 14, 12
  ),
  DepartmentsHier(Id, HumanId, HumanIdPath, Name, NamePath, Level, SortKey) as (
  select d.Id,
         row_number() over (order by d.id),
         cast(row_number() over (order by d.id) as nvarchar(16)),
         dn.Name,
         cast(dn.Name as nvarchar(256)),
         1,
         row_number() over (order by d.id) * power(100, 2)
    from Departments d, DepartmentName dn
   where d.IdParentDepartment is null
     and dn.Id = d.Id
   union all
  select d.Id,
         row_number() over (order by d.id),
         HumanIdPath = cast(dh.HumanIdPath + '.' + cast(row_number() over (order by d.id) as nvarchar(256)) as nvarchar(16)),
         dn.Name,
         NamePath = cast(dh.NamePath + ' > ' + dn.name as nvarchar(256)),
         dh.Level + 1,
         dh.SortKey + row_number() over (order by d.id) * power(100, 2 - dh.Level)
    from DepartmentsHier dh, Departments d, DepartmentName dn
   where d.IdParentDepartment = dh.Id
     and dn.Id = d.Id
  )
select dh.HumanIdPath, dh.Name, dh.NamePath
  from DepartmentsHier dh
 order by dh.SortKey;
Вывод:
HumanIdPath Name NamePath ---------------- ----------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 Административный департамент Административный департамент 1.1 Административный департамент Административный департамент > Административный департамент 1.2 Договорной отдел Административный департамент > Договорной отдел 1.3 Общий отдел Административный департамент > Общий отдел 1.4 Отдел закупок Административный департамент > Отдел закупок 1.5 Отдел протокольного сопровождения Административный департамент > Отдел протокольного сопровождения 1.6 Управление административно-хозяйственной деятельности Административный департамент > Управление административно-хозяйственной деятельности 1.6.1 Отдел ИТ Административный департамент > Управление административно-хозяйственной деятельности > Отдел ИТ 1.6.2 Управление административно-хозяйственной деятельности Административный департамент > Управление административно-хозяйственной деятельности > Управление административно-хозяйственной деятельности 1.7 Управление безопасности Административный департамент > Управление безопасности 1.8 Управление по обеспечению безопасности Административный департамент > Управление по обеспечению безопасности 2 Академия Умные дороги Академия Умные дороги 2.1 Академия Умные дороги Академия Умные дороги > Академия Умные дороги 2.2 Отдел сетевых программ Академия Умные дороги > Отдел сетевых программ
...
Рейтинг: 0 / 0
06.01.2025, 14:14
| Ответить | Цитировать | Написать  
Запрос на выборку иерархии родителей
    #40139320
Фотография Green2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Green2Привилегированный пользователь
Участник
AlenaAAAA [игнорируется] 

Я бы сделал временными таблицами.
Например, первой таблицей отфильтровал верхние уровни.
Второй таблицей отфильтровал подчиненных первому уровню
третьей таблицей отфильтровал третий уровень административный
Четвертым запросом объединил бы все предыдущие таблицы с полем сортировки по номеру отдела.
...
Рейтинг: 0 / 0
09.01.2025, 15:24
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Запрос на выборку иерархии родителей
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&gotonew=1&tid=2187108]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 31ms
get topic data:
 11ms
get first new msg:
 7ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 14ms
total:  149ms
созд. / загр.: 149ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]