Добро пожаловать в еще одну статью из нашей серии задач по запросам SQL Server! В этой статье мы рассмотрим, как создать иерархический вывод с использованием рекурсии в SQL Server.

Представьте, что вы работаете в небольшом розничном магазине классических автомобилей под названием Prestige Cars Ltd. Генеральный директор запросил список сотрудников и их менеджеров, а также их уровни в структуре компании. Для выполнения этой задачи мы будем использовать таблицу Staff в базе данных компании.

Давайте посмотрим на код:

WITH HierarchyList_CTE
AS
(
SELECT    StaffID, StaffName, Department, ManagerID, 1 AS StaffLevel
FROM      Reference.Staff
WHERE     ManagerID IS NULL
UNION ALL
SELECT         ST.StaffID, ST.StaffName
              ,ST.Department, ST.ManagerID, StaffLevel + 1
FROM          Reference.Staff ST
INNER JOIN    HierarchyList_CTE CTE
              ON ST.ManagerID = CTE.StaffID
)
SELECT         STF.Department
              ,STF.StaffName
              ,CTE.StaffName AS ManagerName
              ,CTE.StaffLevel 
FROM          HierarchyList_CTE CTE
INNER JOIN    Reference.Staff STF
              ON STF.ManagerID = CTE.StaffID

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

Код начинается с корневого запроса, который определяет начальную точку для иерархии. Затем добавляется рекурсивный запрос, который объединяет таблицу Staff с самим CTE, создавая иерархию на основе ManagerID. Счетчик увеличивается каждый раз, когда в таблице Staff находится новый уровень, указывая глубину иерархии сотрудников. Рекурсия останавливается, когда у менеджера нет дальнейших уровней сотрудников ниже.

Чтобы отфильтровать вывод по определенному отделу, вы можете добавить несколько строк кода, например, так:

WHERE         STF.Department = 'Финансы'
ORDER BY      CTE.StaffLevel

Это позволит отображать только сотрудников в отделе Финансы.

Создание иерархического вывода в SQL Server может быть мощным инструментом для визуализации организационных структур или любых других иерархических данных. Используя рекурсию и рекурсивное CTE, вы легко можете перемещаться по уровням иерархии и отображать нужную информацию.

Это все для этой статьи! Следите за нашей серией задач по запросам SQL Server. Если вы хотите узнать больше о запросах в SQL Server, ознакомьтесь с нашей книгой “Query Answers with SQL Server Volume II: In-Depth Querying” Адама и Карин Аспин, доступной в печатном и электронном форматах.

Спасибо за чтение!