GETLEVEL: примеры (SQL)

Примеры получения уровня в иерархии

Пример с рекурсивным CTE для таблицы сотрудников:

WITH EmployeeCTE AS (
    -- Якорь рекурсии: выбор корневых менеджеров
    SELECT 
        EmployeeID,
        ManagerID,
        EmployeeName,
        0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- Рекурсивный член: подчиненные
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        cte.Level + 1
    FROM Employees e
    INNER JOIN EmployeeCTE cte ON e.ManagerID = cte.EmployeeID
)
SELECT * FROM EmployeeCTE ORDER BY Level, EmployeeName;

EmployeeID | ManagerID | EmployeeName | Level
-----------|-----------|--------------|------
1          | NULL      | Иванов       | 0
2          | 1         | Петров       | 1
3          | 1         | Сидоров      | 1
4          | 2         | Кузнецов     | 2

Пример использования метода GetLevel() для типа hierarchyid:

-- Создание таблицы с hierarchyid
CREATE TABLE OrgStructure
(
    NodeId hierarchyid PRIMARY KEY,
    NodeName nvarchar(50)
);

-- Вставка данных
INSERT INTO OrgStructure VALUES
('/', 'Компания'),
('/1/', 'Отдел разработки'),
('/1/1/', 'Проект А'),
('/2/', 'Отдел продаж');

-- Получение уровня для каждого узла
SELECT 
    NodeId.ToString() AS Path,
    NodeName,
    NodeId.GetLevel() AS Level
FROM OrgStructure
ORDER BY NodeId;

Path    | NodeName           | Level
--------|--------------------|------
/       | Компания           | 0
/1/     | Отдел разработки   | 1
/1/1/   | Проект А           | 2
/2/     | Отдел продаж       | 1

Похожие методы и подходы в MS SQL

Рекурсивные CTE - основной и наиболее гибкий способ работы с иерархическими данными. Позволяют строить сложные запросы с фильтрацией, агрегацией и сортировкой по уровням.

Тип данных hierarchyid - специальный тип для эффективного хранения и запросов деревьев. Включает методы: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendantOf. Предпочтителен для часто изменяющихся или глубоких иерархий.

Временные таблицы и курсоры - устаревший, но иногда применяемый метод для обхода деревьев в сложных сценариях.

Рекурсивные CTE используют, когда нужна максимальная гибкость или при работе с существующими таблицами. Hierarchyid выбирают для новых разработок, где важна производительность при частых операциях вставки/перемещения узлов.

Аналоги в других СУБД

Oracle предоставляет оператор CONNECT BY для иерархических запросов:

SELECT 
    EmployeeID,
    ManagerID,
    EmployeeName,
    LEVEL
FROM Employees
START WITH ManagerID IS NULL
CONNECT BY PRIOR EmployeeID = ManagerID;

PostgreSQL использует рекурсивные CTE, аналогично SQL Server, но с поддержкой поисковых рекурсий (WITH RECURSIVE ... SEARCH):

WITH RECURSIVE EmployeeCTE AS (
    SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, c.Level + 1
    FROM Employees e
    JOIN EmployeeCTE c ON e.ManagerID = c.EmployeeID
) 
SELECT * FROM EmployeeCTE;

MySQL (версии 8.0+) также поддерживает рекурсивные CTE с синтаксисом, похожим на PostgreSQL и SQL Server.

SQLite с версии 3.8.3 поддерживает рекурсивные CTE через ключевое слово RECURSIVE в выражении WITH.

Типичные ошибки

1. Бесконечная рекурсия при циклических ссылках:

-- Данные с циклом: сотрудник 5 является менеджером для 6, а 6 - для 5
WITH RecursiveCTE AS (
    SELECT EmployeeID, ManagerID, 0 AS Level
    FROM Employees WHERE EmployeeID = 5
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, r.Level + 1
    FROM Employees e
    INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
SELECT * FROM RecursiveCTE;
-- Ошибка: Превышено максимальное количество рекурсии 100.

Решение: установка MAXRECURSION или проверка на циклы.

2. Неправильное условие соединения в рекурсивной части CTE, приводящее к неправильным уровням или дублированию.

3. Использование метода GetLevel() для значения hierarchyid, которое не инициализировано (NULL).

Изменения в последних версиях

В SQL Server 2017 и более новых версиях улучшена производительность рекурсивных CTE благодаря оптимизациям обработчика запросов.

Для типа hierarchyid существенных изменений в методах, включая GetLevel(), не было. Основные улучшения связаны с интеграцией и производительностью.

В Azure SQL Database и SQL Server 2019+ доступны ускоренные обработки рекурсивных запросов в отдельных сценариях.

Расширенные примеры

Пример с ограничением глубины рекурсии и вычислением пути:

WITH EmployeePathCTE AS (
    SELECT 
        EmployeeID,
        ManagerID,
        EmployeeName,
        0 AS Level,
        CAST(EmployeeName AS NVARCHAR(MAX)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        cte.Level + 1,
        CAST(cte.Path + ' -> ' + e.EmployeeName AS NVARCHAR(MAX))
    FROM Employees e
    INNER JOIN EmployeePathCTE cte ON e.ManagerID = cte.EmployeeID
    WHERE cte.Level < 5 -- Ограничение глубины
)
SELECT EmployeeID, EmployeeName, Level, Path 
FROM EmployeePathCTE 
WHERE Level > 0
ORDER BY Path;

Пример с использованием hierarchyid для поиска подчиненных на определенном уровне:

DECLARE @ManagerNode hierarchyid;
SELECT @ManagerNode = NodeId 
FROM OrgStructure 
WHERE NodeName = 'Отдел разработки';

SELECT 
    NodeId.ToString() AS Path,
    NodeName,
    NodeId.GetLevel() AS Level
FROM OrgStructure
WHERE NodeId.IsDescendantOf(@ManagerNode) = 1
    AND NodeId.GetLevel() = @ManagerNode.GetLevel() + 2;
-- Найдет все узлы, находящиеся на 2 уровня ниже отдела разработки

Пример с агрегацией по уровням иерархии:

WITH EmployeeCTE AS (
    SELECT EmployeeID, ManagerID, Salary, 0 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Salary, cte.Level + 1
    FROM Employees e
    INNER JOIN EmployeeCTE cte ON e.ManagerID = cte.EmployeeID
)
SELECT 
    Level,
    COUNT(*) AS EmployeeCount,
    AVG(Salary) AS AverageSalary
FROM EmployeeCTE
GROUP BY Level
ORDER BY Level;