Анализ семейного дерева

Иерархические и рекурсивные запросы в SQL Server

Jasmin Ludolf

Content Developer

Семейное дерево

Таблица familyTree содержит:

ID — идентификатор человека
Name — имя человека
parentID — идентификатор родителя

Поля, описывающие иерархию:

ID
parentID

Изображение семейного дерева

Собираем всё вместе

Принципы работы рекурсивных CTE:

Инициализируйте рекурсию в опорном элементе
Реализуйте рекурсивную функцию в рекурсивном элементе
Определите условие завершения

Дополнительные рабочие принципы:

Получите уровень рекурсии
Объедините результаты рекурсии в одно поле

Вопросы о семейном дереве

Подсчёт числа поколений

Определите LEVEL

-- Anchor member
0 as LEVEL
-- Recursive member
LEVEL + 1

Подсчитайте количество уровней для получения поколений: COUNT(LEVEL)

Generations:
100

Вопросы о семейном дереве

Все возможные родители в одном поле

Объедините результаты рекурсии в одно поле

-- Anchor member
CAST(ID AS VARCHAR(MAX)) as Parent
-- Recursive member
Parent + ' -> ' + CAST(parentID AS VARCHAR(MAX))

+---------------------------------------------+
| Name            | Parent                    |
|-----------------|---------------------------|
|Dominik Egarter  | 100 -> 101 -> 102 ->103   |
+---------------------------------------------+

Давайте потренируемся!

Иерархические и рекурсивные запросы в SQL Server