更新时间:2021-12-04 23:11:08
以下内容可能适用于您没有测试它,所以它可能需要一些调整)。看起来像你所要做的就是删除层次结构底部的员工,然后再删除高层。使用CTE递归构建删除层次,并将CTE输出按员工的层次结构级别降序排列。然后按顺序删除。
The below might work for you (I haven't tested it so it may require some tweaking). Seems like all you have to do is delete the employees from the bottom of the hierarchy before you delete the ones higher-up. Use a CTE to build the delete hierarchy recursively and order the CTE output descending by the hierarchy level of the employee. Then delete in order.
CREATE PROC usp_DeleteEmployeeAndSubordinates (@empId INT)
AS
;WITH employeesToDelete AS (
SELECT id, CAST(1 AS INT) AS empLevel
FROM employee
WHERE id = @empId
UNION ALL
SELECT e.id, etd.empLevel + 1
FROM employee e
JOIN employeesToDelete etd ON e.boss_id = etd.id AND e.boss_id != e.id
)
SELECT id, ROW_NUMBER() OVER (ORDER BY empLevel DESC) Ord
INTO #employeesToDelete
FROM employeesToDelete;
DECLARE @current INT = 1, @max INT = @@ROWCOUNT;
WHILE @current <= @max
BEGIN
DELETE employee WHERE id = (SELECT id FROM #employeesToDelete WHERE Ord = @current);
SET @current = @current + 1;
END;
GO