Как перемещаться по дереву/работать с иерархическими данными в коде SQL

Скажем, у меня есть таблица сотрудников с записью для каждого сотрудника в моей компании и столбец для супервизора (как показано ниже). Я хотел бы подготовить отчет, в котором перечислены имена и названия для каждого шага в контрольной строке. например, для dick robbins, 1d # 15, я бы хотел, чтобы список каждого руководителя в его "цепочке команд" до самого президента, большой сыр. Я бы хотел избежать использования курсоров, но если это единственный способ сделать это, то это нормально.

id  fname   lname   title   supervisorid
1   big     cheese  president   1
2   jim     william vice president  1
3   sally   carr    vice president  1
4   ryan    allan   senior manager  2
5   mike    miller  manager 4
6   bill    bryan   manager 4
7   cathy   maddy   foreman 5
8   sean    johnson senior mechanic 7
9   andrew  koll    senior mechanic 7 
10  sarah   ryans   mechanic    8
11  dana    bond    mechanic    9
12  chris   mcall   technician  10
13  hannah  ryans   technician  10
14  matthew miller  technician  11
15  dick    robbins technician  11

Реальные данные, вероятно, будут не более 10 уровней глубины... но я бы предпочел не просто сделать 10 внешних объединений... Я надеялся, что есть что-то лучше, чем это, и менее привлекателен, чем курсоры.

Спасибо за любую помощь.

Ответ 1

Это, по сути, порт принятого ответа на мой вопрос, который я связал с комментариями OP.

вы можете использовать выражения common-table

WITH Family As 
( 
    SELECT e.id, e.supervisorid, 0 as Depth
    FROM Employee e
    WHEREid = @SupervisorID 
    UNION All 
    SELECT e2.ID, e2.supervisorid, Depth + 1
    FROM Employee e2
        JOIN Family 
            On Family.id = e2.supervisorid 
) 
SELECT*
FROM Family

Для большего:

Рекурсивные запросы с использованием общих выражений таблицы

Ответ 2

Некоторая рекурсивная функция, которая возвращает супервизора (если есть) или null. Может быть SP, который вызывает себя также, и используя UNION.

Ответ 3

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

Вам просто нужно добавить столбец, который содержит всю родословную объекта. Например, в таблице ниже tree_path столбец с именем tree_path:

+----+-----------+----------+----------+
| id | value     | parent   | tree_path|
+----+-----------+----------+----------+
|  1 | Some Text |        0 |          |
|  2 | Some Text |        0 |          |
|  3 | Some Text |        2 |       -2-|
|  4 | Some Text |        2 |       -2-|
|  5 | Some Text |        3 |     -2-3-|
|  6 | Some Text |        3 |     -2-3-|
|  7 | Some Text |        1 |       -1-|
+----+-----------+----------+----------+

Выбор всех потомков записи с id = 2 выглядит так:

SELECT * FROM comment_table WHERE tree_path LIKE '-2-%' ORDER BY tree_path ASC

Чтобы построить дерево, вы можете отсортировать по tree_path чтобы получить массив, который довольно легко преобразовать в дерево.

Вы также можете индексировать tree_path и индекс может использоваться, когда шаблон не находится в начале.

Например, tree_path LIKE '-2-%' может использовать индекс, но tree_path LIKE % -2- '' не может.