▼

Обход дерева данных в таблице MS SQL Server (рекурсивный запрос)

Дано: база на MS SQL Server с таблицей, где перечислены разделы (подразделения или другие данные, которые можно представить в виде иерархии).
Задача: запросом по идентификатору раздела получить:
1) список всех дочерних элементов
2) список всех родительских элементов
Решение: использовать рекурсивные запросы при помощи команды WITH Recursive
Рассмотрим пример…

Допустим имеется таблица подразделений вуза (tblDepartment) в формате:
intID - strDepartmentName - intParentID
Где,
intID - идентификатор подразделения,
strDepartmentName  - наименование подразделения,
intParentID - идентификатор родительского подразделения.

Корневой элемент будет ссылаться в качестве "родителя" на самого себя.
Пример данных:


Итак, чтобы получить все дочерние элементы по идентификатору родителя (т.е. узнать все подчиненные подразделения по выбранному родительскому подразделению) указываем следующий рекурсивный SQL-запрос:

WITH Recursive (intID, intParentID, strDepartmentName)
AS
(
    SELECT intID, intParentID, strDepartmentName
    FROM tblDepartment e
    WHERE e.intID = 9 
    UNION ALL
    SELECT e.intID, e.intParentID, e.strDepartmentName
    FROM tblDepartment e
        JOIN Recursive r ON e.intParentID = r.intID
)
SELECT intID, intParentID, strDepartmentName
FROM Recursive r

В строке "WHERE e.intID = 9" указываем идентификатор исходного подразделения.


И наоборот, чтобы получить все родительские элементы по идентификатору дочернего (т.е. узнать все вышестоящие подразделения по выбранному подчиненному подразделению) указываем следующий рекурсивный SQL-запрос:

WITH Recursive (intID, intParentID, strDepartmentName)
AS
(
    SELECT intID, intParentID, strDepartmentName
    FROM tblDepartment e
    WHERE e.intID = 35 
    UNION ALL
    SELECT e.intID, e.intParentID, e.strDepartmentName
    FROM tblDepartment e
        JOIN Recursive r ON e.intID = r.intParentID
        WHERE e.intID <> e.intParentID
)
SELECT top 15 intID, intParentID, strDepartmentName
FROM Recursive r

В строке "WHERE e.intID = 35" указываем идентификатор исходного подразделения.


Таким образом можно строить рекурсивные запросы для обращения таблицы к самой себе.

(с) Ella S.
Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.
Дата:

3 комментария:

  1. Unknown14 мая 2018 г. в 10:10

    Запрос вывода всех родителей не выводит корневой...

    ОтветитьУдалить
  2. Анонимный17 июля 2019 г. в 12:44

    Спасибо за полезную информацию! Еще хорошо бы добавить в запросы вывод уровня в иерархии

    ОтветитьУдалить
  3. Алеша4 августа 2021 г. в 15:49


    wITH Recursive (group_id, parent_id, name, lev)
    AS
    (
    SELECT group_id, parent_id, name, 1 lev
    FROM good_groups e
    WHERE e.group_id = 956
    UNION ALL
    SELECT e.group_id, e.parent_id, e.name, lev+1 lev
    FROM good_groups e
    JOIN Recursive r ON e.group_id = r.parent_id
    WHERE e.group_id <> e.parent_id
    )

    SELECT group_id, parent_id, name, lev
    FROM Recursive r

    ОтветитьУдалить