Как превратить эту операцию курсора в операцию на основе набора в иерархии замыкания?
- Trees.DirectReports — это замыкающая (иерархическая) таблица.
- Существует таблица под названием
Usersс:RowID, EmployeeId, and MangerId.@RowCount— количество записей в этой таблице, а#EmpMgr— курсор для этой таблицы.
Ниже приведен соответствующий код sql, который я хотел бы преобразовать из операции на основе курсора в операцию на основе набора.
WHILE @RowCount <= @NumberRecords --loop through each record in Users table
BEGIN
SET @EmpId = (SELECT EmployeeId FROM #EmpMgr WHERE RowID = @RowCount)
SET @MgrId = (SELECT ManagerId FROM #EmpMgr WHERE RowID = @RowCount)
INSERT INTO [Trees].[DirectReports](EmployeeId, ManagerId, Depth)
SELECT c.EmployeeId, p.ManagerId, p.Depth + c.Depth + 1
FROM Trees.DirectReports p join Trees.DirectReports c
WHERE p.EmployeeId = @MgrId AND c.ManagerId = @EmpId
SET @RowCount = @RowCount + 1
END
Поэтому мне очень хотелось бы выяснить, как сделать это в виде заданного запроса, потому что я знаю, что так было бы намного быстрее, но мой мозг сегодня не совсем правильно устанавливает связи, чтобы понять это.
*Обратите внимание, что для ответа на этот вопрос вам нужно уже понимать, как работают замыкающие таблицы. В противном случае вышеизложенное, вероятно, не будет иметь смысла.
Ответы 1
Нашел то, что искал, с помощью пары других сообщений. Основной ответ таков:
WITH cte AS
(
SELECT LegacyId ancestor, LegacyId descendant, 0 depth FROM Users
UNION ALL
SELECT cte.ancestor, u.LegacyId descendant, cte.depth + 1 depth
FROM dbo.Users u JOIN cte ON u.ManagerId = cte.descendant
)
select * from cte
Однако поначалу меня сбило с толку то, что были некоторые неверные данные, вызывающие циклические зависимости. Я смог использовать следующий запрос, чтобы определить, где были эти экземпляры:
with cte (id,pid,list,is_cycle)
as
(
select legacyid id, managerid pid,',' + cast (legacyid as varchar(max)) + ',',0
from users
union all
select u.legacyid id,
u.managerid pid,
cte.list + cast(u.legacyid as varchar(10)) + ',' ,case when cte.list like '%,' + cast (u.legacyid as varchar(10)) + ',%' then 1 else 0 end
from cte join users u on u.managerid = cte.id
where cte.is_cycle = 0
)
select *
from cte
where is_cycle = 1
Как только я исправил циклические данные, все заработало отлично. Ознакомьтесь со следующими сообщениями SO для получения дополнительной информации, поскольку это то, что я использовал для своего решения: Есть ли способ обнаружить цикл в иерархических запросах в SQL Server? и Как создать замыкающую таблицу, используя данные из списка смежности?.