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

Краткие примеры использования

Пример 1: Создание первого потомка для корневого узла.

DECLARE @parent hierarchyid = hierarchyid::GetRoot(); -- '/'
SELECT @parent.GetDescendant(NULL, NULL).ToString();

/1/

Пример 2: Создание потомка после указанного узла (child1).

DECLARE @parent hierarchyid = '/1/';
DECLARE @child1 hierarchyid = '/1/1/';
SELECT @parent.GetDescendant(@child1, NULL).ToString();

/1/2/

Пример 3: Создание потомка перед указанным узлом (child2).

DECLARE @parent hierarchyid = '/1/';
DECLARE @child2 hierarchyid = '/1/3/';
SELECT @parent.GetDescendant(NULL, @child2).ToString();

/1/2/

Пример 4: Создание потомка между двумя указанными узлами.

DECLARE @parent hierarchyid = '/1/';
DECLARE @child1 hierarchyid = '/1/1/';
DECLARE @child2 hierarchyid = '/1/3/';
SELECT @parent.GetDescendant(@child1, @child2).ToString();

/1/2/

Похожие функции в MS SQL

Для работы с типом данных hierarchyid существует несколько других методов:

• GetRoot() — статический метод для получения корневого узла иерархии (значение '/'). Используется для начального заполнения таблицы.
• GETANCESTOR(n) — возвращает предка узла на n уровней выше. Полезен для навигации вверх по дереву.
• GetDescendant является основным методом для добавления новых узлов. Для поиска существующих потомков используют запросы с операторами сравнения или метод ISDESCENDANTOF.
• GETLEVEL() — возвращает целое число, указывающее глубину узла в иерархии (уровень). Используется для фильтрации или форматирования вывода.
• ToString() — преобразует значение hierarchyid в его строковое представление.

Функция GetDescendant предпочтительна именно для генерации новых идентификаторов при вставке, в то время как другие методы служат для навигации и анализа существующей структуры.

Альтернативы в других СУБД

В других базах данных для представления иерархий используют различные подходы:

PostgreSQL (с модулем ltree): Используются метки пути. Для добавления дочернего узла применяют конкатенацию.

SELECT 'Top.Science'::ltree;
-- Добавление потомка
SELECT 'Top.Science.Astronomy'::ltree;

Top.Science
Top.Science.Astronomy

Oracle (с иерархическими запросами CONNECT BY): Иерархия часто строится на основе столбцов parent_id. Новый ID для потомка обычно генерируется последовательностью, а связь определяется через parent_id. Функции типа SYS_CONNECT_BY_PATH возвращают путь.

INSERT INTO employees (id, name, manager_id)
VALUES (seq.nextval, 'John', 100);

MySQL, SQLite, Sybase ASE: Отсутствует встроенный тип данных, аналогичный hierarchyid. Используют паттерн Adjacency List (столбец parent_id) или Nested Sets. Генерация пути требует применения рекурсивных запросов или обработки на уровне приложения. В MySQL 8+ доступны рекурсивные CTE.

Ключевое отличие MS SQL функции GetDescendant — она детерминированно вычисляет физический путь нового узла на основе существующей структуры, в то время как в других системах связь часто логическая (через ссылки на ID родителя).

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

1. Передача одинаковых не-NULL значений для child1 и child2. Функция требует, чтобы они были различны, если оба указаны.

DECLARE @parent hierarchyid = '/1/';
DECLARE @child hierarchyid = '/1/1/';
SELECT @parent.GetDescendant(@child, @child).ToString();

Сообщение об ошибке: Ошибка GetDescendant failed because child values are not different. Параметры 0x58, 0x58.

2. Передача потомков, не относящихся к указанному родителю. Аргументы child1 и child2 должны быть непосредственными детьми узла parent.

DECLARE @parent hierarchyid = '/1/';
DECLARE @notAChild hierarchyid = '/2/1/';
SELECT @parent.GetDescendant(@notAChild, NULL).ToString();

Сообщение об ошибке: Ошибка GetDescendant failed because the specified child values are not children of the parent. Параметры 0x58, 0x68.

3. Использование с неправильным порядком аргументов. child1 должен быть меньше child2.

DECLARE @parent hierarchyid = '/1/';
DECLARE @child1 hierarchyid = '/1/3/';
DECLARE @child2 hierarchyid = '/1/1/';
SELECT @parent.GetDescendant(@child1, @child2).ToString();

Сообщение об ошибке: Ошибка GetDescendant failed because the specified child values are not ordered. Параметры 0x5A, 0x5E.

4. Попытка вставить узел в уже заполненный диапазон. Если между child1 и child2 нет свободного места, функция завершится ошибкой, так как тип hierarchyid имеет ограниченную емкость на одном уровне.

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

Функция GetDescendant, как метод типа данных hierarchyid, была представлена в SQL Server 2008 и с тех пор её базовое поведение и синтаксис не претерпели значительных изменений в основных выпусках (вплоть до SQL Server 2022).

Основные улучшения, связанные с иерархическими данными, в более новых версиях касаются производительности запросов, использующих тип hierarchyid, и улучшения работы с индексами. Сама функция остаётся стабильной.

Рекомендуется обращаться к документации по конкретной версии SQL Server для актуальной информации о совместимости уровня базы данных, однако для GetDescendant обратная совместимость полностью сохраняется.

Расширенные примеры использования

Пример 1: Вставка нового сотрудника в организационную иерархию в алфавитном порядке имен среди соседей.

CREATE TABLE Employees (
    NodeId hierarchyid PRIMARY KEY,
    Name nvarchar(50)
);

-- Вставляем корневого руководителя
INSERT INTO Employees (NodeId, Name)
VALUES (hierarchyid::GetRoot(), 'Директор');

DECLARE @Manager hierarchyid = (SELECT NodeId FROM Employees WHERE Name = 'Директор');
-- Вставляем первого подчиненного
INSERT INTO Employees (NodeId, Name)
VALUES (@Manager.GetDescendant(NULL, NULL), 'Анна');

-- Вставляем второго подчиненного после Анны
INSERT INTO Employees (NodeId, Name)
VALUES (@Manager.GetDescendant(
    (SELECT NodeId FROM Employees WHERE Name = 'Анна'),
    NULL
), 'Борис');

-- Вставляем третьего подчиненного между Анной и Борисом (например, 'Алексей' по алфавиту)
-- Сначала удалим Бориса для перестановки
DELETE Employees WHERE Name = 'Борис';
-- Вставляем Алексея после Анны
INSERT INTO Employees (NodeId, Name)
VALUES (@Manager.GetDescendant(
    (SELECT NodeId FROM Employees WHERE Name = 'Анна'),
    NULL
), 'Алексей');
-- Теперь вставляем Бориса после Алексея
INSERT INTO Employees (NodeId, Name)
VALUES (@Manager.GetDescendant(
    (SELECT NodeId FROM Employees WHERE Name = 'Алексей'),
    NULL
), 'Борис');

SELECT NodeId.ToString() AS Path, Name FROM Employees ORDER BY Path;

Path    Name
/       Директор
/1/     Анна
/2/     Алексей
/3/     Борис

Пример 2: Пакетное добавление нескольких узлов в транзакции с использованием переменных для промежуточных значений hierarchyid.

BEGIN TRANSACTION;
DECLARE @Parent hierarchyid = '/1/2/';
DECLARE @NewChild1 hierarchyid, @NewChild2 hierarchyid;

-- Генерируем первого потомка
SET @NewChild1 = @Parent.GetDescendant(NULL, NULL);
INSERT INTO TestHierarchy (NodeId, Name) VALUES (@NewChild1, 'Потомок 1');

-- Генерируем второго потомка после первого
SET @NewChild2 = @Parent.GetDescendant(@NewChild1, NULL);
INSERT INTO TestHierarchy (NodeId, Name) VALUES (@NewChild2, 'Потомок 2');

COMMIT TRANSACTION;

Пример 3: Использование в табличной функции для генерации поддерева заданной глубины (например, для тестовых данных).

CREATE FUNCTION GenerateTree(@root hierarchyid, @depth int)
RETURNS @Tree TABLE (NodeId hierarchyid, Level int)
AS
BEGIN
    INSERT INTO @Tree VALUES (@root, 0);
    DECLARE @current_level int = 0;
    WHILE @current_level < @depth
    BEGIN
        INSERT INTO @Tree
        SELECT parent.GetDescendant(NULL, NULL), @current_level + 1
        FROM @Tree AS parent
        WHERE parent.Level = @current_level;
        SET @current_level = @current_level + 1;
    END
    RETURN;
END;

SELECT NodeId.ToString(), Level FROM dbo.GenerateTree(hierarchyid::GetRoot(), 2);

(No column name)    Level
/                   0
/1/                 1
/2/                 1
/1/1/               2
/1/2/               2
/2/1/               2
/2/2/               2

Пример 4: Перестроение порядка потомков на одном уровне без изменения их внутренней иерархии (например, сортировка по полю Дата). Это сложная операция, требующая временного хранения данных.

-- Допустим, есть узлы /1/1/, /1/2/, /1/3/. Требуется изменить их порядок на /1/3/, /1/1/, /1/2/ на основе какого-либо поля.
-- Упрощенный пример с использованием временной нумерации и CTE.
WITH SortedChildren AS (
    SELECT NodeId, Name,
           ROW_NUMBER() OVER (ORDER BY HireDate DESC) AS NewOrder -- Сортировка по дате приема
    FROM Employees
    WHERE NodeId.GetAncestor(1) = '/1/'
)
-- Обновление требует сложной логики, часто проще пересоздать поддерево.
-- GetDescendant здесь может использоваться в процессе построения нового порядка.