以下内容可能对您有用（我尚未测试过，因此可能需要进行一些调整）。似乎您要做的就是从层次结构的底部删除雇员，然后再删除较高的雇员。使用CTE递归地构建删除层次结构，并按雇员的层次结构级别排列CTE输出降序。然后按顺序删除。
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
602392714
清零编程群