Время узнать о иерархии. Во-первых, некоторый код:
IF object_id('tempdb.dbo.#employees') IS NOT NULL
DROP TABLE #employees;
go
WITH Employees AS (
SELECT *
FROM ( VALUES
( 1, NULL, 5, 'CEO'),
( 2, 1, 4, 'EMP'),
( 3, 1, 4, 'ORG'),
( 4, 2, 3, NULL ),
( 5, 2, 3, NULL ),
( 6, 2, 2, NULL ),
( 7, 1, 1, NULL ),
( 8, 5, 0, NULL )
) AS x ( EmpId, ManagerId, Level, Value )
), rcte AS (
SELECT e.EmpId ,
e.ManagerId ,
e.Level ,
e.Value,
CAST('/' + CAST(e.EmpId AS VARCHAR) + '/' AS VARCHAR(MAX)) AS h
FROM Employees AS e
WHERE e.ManagerId IS NULL
UNION ALL
SELECT e.EmpId ,
e.ManagerId ,
e.Level ,
e.Value ,
m.h + CAST(e.EmpId AS VARCHAR) + '/' AS h
FROM Employees AS e
JOIN rcte AS m
ON e.ManagerId = m.EmpId
)
SELECT rcte.EmpId ,
rcte.ManagerId ,
rcte.Level ,
rcte.Value ,
CAST(rcte.h AS HIERARCHYID) AS h
INTO #employees
FROM rcte;
GO
SELECT e.EmpId ,
e.ManagerId ,
e.Level ,
e.Value ,
e.h.ToString() AS h
FROM #employees AS e
JOIN #employees AS m
ON e.h.IsDescendantOf(m.h) = 1
WHERE m.EmpId = 1
SELECT m.EmpId ,
m.ManagerId ,
m.Level ,
m.Value ,
m.h.ToString() AS h
FROM #employees AS e
JOIN #employees AS m
ON e.h.IsDescendantOf(m.h) = 1
WHERE e.EmpId = 8
В то время как мне нужен рекурсивный CTE для фактической установки иерархии, любой из реальных запросов формы «кто этот человек сообщает?» и «кто подчиняется этому человеку?» в конечном итоге удовлетворяются из сохраненной иерархии в таблице #employees. Два запроса в конце показывают, как пройти иерархию в любом направлении. Это важно, если ваша иерархия большая (широкая, глубокая или и то, и другое). Вам нужно сохранить его, когда диаграмма org изменится, но это одноразовая операция. Запросы данных должны быть быстрыми, потому что линия сохраняется с записью сотрудника.
Кстати, ваш столбец Level
для меня немного странный. В частности, это выглядит наоборот (т. Е. CEO имеет самый высокий уровень). Я говорю это, потому что если / когда вы добавляете еще один уровень в диаграмму org, вам нужно будет переопределить всех от генерального директора. Если у вас есть генеральный директор с самым низким уровнем, вы просто придерживаетесь этого уровня на дне и не должны переоценивать кого-либо.