GETANCESTOR: примеры (SQL)
GETANCESTOR(n): hierarchyidОсновы функции GETANCESTOR
Функция GETANCESTOR в MS SQL Server возвращает идентификатор предка в иерархических структурах данных, представленных с помощью типа данных hierarchyid. Она применяется при работе с древовидными структурами, например, в организационных диаграммах, категориях товаров или меню веб-сайтов.
Синтаксис функции:
hierarchyid::GetAncestor ( n )
Аргумент n имеет целочисленный тип и указывает, на сколько уровней вверх по иерархии необходимо подняться для получения предка. Значение должно быть неотрицательным числом.
Возвращаемое значение — объект hierarchyid, представляющий собой идентификатор предка на указанном уровне. Если аргумент n превышает глубину узла, функция возвращает NULL. Для корневого узла вызов GetAncestor(0) возвращает сам корень.
Простые примеры использования
Пример 1. Получение непосредственного родителя узла.
DECLARE @child hierarchyid = '/1/1/3/'; SELECT @child.GetAncestor(1).ToString();/1/1/
Пример 2. Получение предка через два уровня.
DECLARE @node hierarchyid = '/1/2/5/1/'; SELECT @node.GetAncestor(2).ToString();/1/2/
Пример 3. Попытка получить предка, когда уровень превышает глубину.
DECLARE @node hierarchyid = '/1/'; SELECT @node.GetAncestor(3) AS Ancestor;NULL
Похожие функции в MS SQL
В MS SQL для работы с hierarchyid существует несколько функций:
- GETDESCENDANT — возвращает дочерний узел между указанными потомками.
- GETLEVEL — возвращает глубину узла в иерархии (целое число).
- GetRoot — возвращает корневой узел иерархии.
- ISDESCENDANTOF — проверяет, является ли узел потомком другого узла.
Предпочтительнее использовать GETANCESTOR при необходимости получения конкретного предка на известном уровне. Для проверки родственных связей удобнее IsDescendantOf, а для навигации по дереву — GetLevel.
Альтернативы в других СУБД
В разных СУБД иерархические запросы реализованы по-разному:
Oracle использует рекурсивные запросы с CONNECT BY или рекурсивные CTE.
SELECT parent_id FROM table_name CONNECT BY PRIOR parent_id = child_id START WITH child_id = 10;PostgreSQL применяет рекурсивные общие табличные выражения (WITH RECURSIVE).
WITH RECURSIVE cte AS ( SELECT id, parent_id FROM categories WHERE id = 10 UNION ALL SELECT p.id, p.parent_id FROM categories p JOIN cte ON p.id = cte.parent_id ) SELECT * FROM cte;MySQL (версии 8.0+) поддерживает рекурсивные CTE, аналогично PostgreSQL.
SQLite не имеет встроенной иерархической поддержки, кроме рекурсивных CTE.
Типичные ошибки
1. Передача отрицательного числа в аргумент n приводит к исключению.
DECLARE @node hierarchyid = '/1/2/'; SELECT @node.GetAncestor(-1);Сообщение 6522: Ошибка во время выполнения. Аргумент для метода GetAncestor не может быть отрицательным.
2. Попытка вызвать метод для NULL-значения hierarchyid.
DECLARE @node hierarchyid = NULL; SELECT @node.GetAncestor(1);NULL
3. Неявное преобразование строки в hierarchyid без проверки формата.
DECLARE @node hierarchyid = '/неверный/путь/';Сообщение 6522: Ошибка во время выполнения. Неверный входной формат hierarchyid.
История изменений
Функция GETANCESTOR появилась в SQL Server 2008 вместе с типом данных hierarchyid. В последующих версиях SQL Server (2012, 2014, 2016, 2017, 2019, 2022) её синтаксис и поведение не изменялись. Все обновления касались общей производительности и интеграции с другими компонентами, но не самой функции.
Расширенные примеры
Пример 1. Поиск всех предков узла с использованием рекурсивного CTE.
WITH Ancestors AS ( SELECT EmpId, NodeId, NodeId.GetLevel() AS Level FROM Employees WHERE EmpId = 15 UNION ALL SELECT e.EmpId, e.NodeId.GetAncestor(1), a.Level - 1 FROM Employees e INNER JOIN Ancestors a ON e.NodeId = a.NodeId.GetAncestor(1) WHERE a.Level > 0 ) SELECT * FROM Ancestors;Пример 2. Получение пути от узла до корня в виде строки.
DECLARE @node hierarchyid = '/1/3/2/1/'; DECLARE @path nvarchar(1000) = ''; DECLARE @level int = @node.GetLevel(); WHILE @level >= 0 BEGIN SET @path = @node.GetAncestor(@level).ToString() + ' -> ' + @path; SET @level = @level - 1; END SELECT LEFT(@path, LEN(@path) - 4) AS FullPath;/ -> /1/ -> /1/3/ -> /1/3/2/ -> /1/3/2/1/
Пример 3. Обновление родителя для группы узлов через изменение их предка.
UPDATE Employees SET NodeId = NodeId.GetReparentedValue( NodeId.GetAncestor(2), -- старый предок NEWID() -- новый предок ) WHERE NodeId.IsDescendantOf('/1/2/') = 1;