我有一個包含 8 條關于員工的記錄的資料庫，它保存了作為每個員工的 id 的 Ssn 和參考其經理的 Super_ssn。下面是創建表的代碼：
Create Table t_employee
(
Ssn int not null,
Super_ssn int,
FirstName varchar(50),
LastName varchar(50),
NationalCode varchar(50),
_role varchar(50),
Primary key(Ssn),
Foreign Key(Super_ssn) references t_employee(Ssn)
);
我必須寫一個帶有Procedure兩個員工的id（例如，@empId1and @empId2），然后執行洗掉操作，條件如下：如果第一個員工沒有任何subalterns，它將被洗掉。但是，如果它有一些 subalterns，則必須先將它們添加到另一個員工 ( @empId2)，然后再將其洗掉。
這是我的代碼：
USE CompanyHierarchy
GO
Create Procedure deleteEmployees (@empId1 int, @empId2 int)
as
Begin
Declare @subCount int;
With CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
(
Select emp.Ssn,
emp.FirstName,
emp.LastName,
emp.Super_ssn,
emp.NationalCode,
emp._role,
0 as _level
From t_employee AS emp
Where emp.Ssn = @empId1
Union ALL
Select _emp.Ssn,
_emp.FirstName,
_emp.LastName,
_emp.Super_ssn,
_emp.NationalCode,
_emp._role,
_emp._level 1
From t_employee _emp
Join CTE1 C1
on _emp.Super_ssn = C1.Ssn
)
Select @subCount = COUNT (*) From CTE1 c1 Where c1.Ssn <> c1.Super_ssn;
IF @subCount = 0
Begin
Delete From t_employee where Ssn = @empId1;
End
ELSE
Begin
With _CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
(
Select emp.Ssn,
emp.FirstName,
emp.LastName,
emp.Super_ssn,
emp.NationalCode,
emp._role,
0 as _level
From t_employee AS emp
Where emp.Ssn = @empId1
Union ALL
Select _emp.Ssn,
_emp.FirstName,
_emp.LastName,
_emp.Super_ssn,
_emp.NationalCode,
_emp._role,
_emp._level 1
From t_employee _emp
Join _CTE1 _C1
on _emp.Super_ssn = _C1.Ssn
)
Update _CTE1 Set Super_ssn = @empId2 Where Super_ssn <> Ssn;
End
End
GO
EXEC deleteEmployees @empId1 = 6, @empId2 = 5;
但它顯示了錯誤
訊息 4421，級別 16，狀態 1，程序 deleteEmployees，第 37 行 [批處理開始第 3 行] 派生表“_CTE1”不可更新，因為派生表的列是派生的或不變的。
我也嘗試實作兩個CTEs 來選擇兩個員工的 subalterns 和 use INSERT，但我不知道如何檢查不添加重復項。
Create Procedure deleteEmployees (@empId1 int, @empId2 int)
as
Begin
Declare @subCount int;
With CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
(
Select emp.Ssn,
emp.FirstName,
emp.LastName,
emp.Super_ssn,
emp.NationalCode,
emp._role,
0 as _level
From t_employee AS emp
Where emp.Ssn = @empId1
Union ALL
Select _emp.Ssn,
_emp.FirstName,
_emp.LastName,
_emp.Super_ssn,
_emp.NationalCode,
_emp._role,
_emp._level 1
From t_employee _emp
Join CTE1 C1
on _emp.Super_ssn = C1.Ssn
)
Select @subCount = COUNT (*) From CTE1 c1 Where c1.Ssn <> c1.Super_ssn;
IF @subCount = 0
Begin
Delete From t_employee where Ssn = @empId1;
End
ELSE
Begin
With _CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
(
Select emp.Ssn,
emp.FirstName,
emp.LastName,
emp.Super_ssn,
emp.NationalCode,
emp._role,
0 as _level
From t_employee AS emp
Where emp.Ssn = @empId1
Union ALL
Select _emp.Ssn,
_emp.FirstName,
_emp.LastName,
_emp.Super_ssn,
_emp.NationalCode,
_emp._role,
_emp._level 1
From t_employee _emp
Join _CTE1 _C1
on _emp.Super_ssn = _C1.Ssn
)
, _CTE2 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
(
Select emp.Ssn,
emp.FirstName,
emp.LastName,
emp.Super_ssn,
emp.NationalCode,
emp._role,
0 as _level
From t_employee AS emp
Where emp.Ssn = @empId1
Union ALL
Select _emp.Ssn,
_emp.FirstName,
_emp.LastName,
_emp.Super_ssn,
_emp.NationalCode,
_emp._role,
_emp._level 1
From t_employee _emp
Join _CTE2 _C2
on _emp.Super_ssn = _C2.Ssn
)
Insert Into _CTE2
Select * From _CTE1;
End
End
我會很感激你的幫助。
uj5u.com熱心網友回復：
您是否使代碼過于復雜？你為什么要遍歷層次結構？當然，如果您打算在“b”報告給“a”而“c”報告給“b”時洗掉“a”，那么您不打算對“c”做任何事情。您只需將這些報告直接更改為“a”（在本例中為“b”）。如果是這樣，您不需要 CTE 來遍歷層次結構。你也養成了壞習慣，選擇了奇怪的命名標準。
只需檢查是否存在@empId1（一個通用名稱，它不提供任何關于其使用方式的線索 - 看看我所說的命名標準是什么意思？）作為主管首先將這些行“移動”（而不是“添加”）到另一個用戶引數@empId2。簡而言之：
update t_employee set Super_ssn = @empId2
where Super_ssn = @empId1;
delete t_employee where Ssn = @empId1;
這就是您在非常基本的級別上需要的所有代碼。添加您希望的任何錯誤處理，可能對引數值進行一些完整性檢查，并可能在更新之前檢查是否存在。使用兩個部分名稱 (schema.table) 是開發的最佳實踐。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/487721.html
上一篇：在遞回地反轉鏈表時，哪個先出現？