如何将一个CTE的输出添加到另一个CTE？

Question

我有一个数据库，该数据库由8个有关员工的记录组成，它既保存了SSN，ssn是每个员工的ID，也可以保存一个引用其经理的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（例如，@empid1 和@empid2 ），然后执行以下条件的删除操作： 如果第一名员工没有任何次级员工，则将被删除。但是，如果它具有某些次数，则必须首先将它们添加到另一名员工（@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;

但是它显示了错误

msg 4421，第16级，状态1，过程删除员工，第37行[批次 启动行3]派生的表'_cte1'无法更新，因为列 派生表的衍生或恒定。

我还尝试实现两个cte s来选择两位员工的子和使用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

我将感谢您的帮助。

Answer 1

您是否使您的代码过度复杂化？您为什么根本穿越层次结构？当然，如果您打算在“ b”报告“ A”和“ C”向“ B”报告时删除“ A”，则您不打算对“ C”做任何事情。您只需要将这些报告直接更改为“ A”（在这种情况下）。如果是这样，则不需要CTE即可穿越层次结构。您还学会了不良习惯，并选择了奇怪的命名标准。

只需检查是否存在@empid1（一个通用名称，该名称不提供有关其使用方式的任何线索 - 请先作为主管作为主管，而不是“移动”（不是Move”（不是“添加”）将这些行到其他用户参数@Empid2。简而言之：

update t_employee set Super_ssn = @empId2
where Super_ssn = @empId1;

delete t_employee where Ssn = @empId1;

这就是您在非常基本的层面上需要的所有代码。添加您想要的任何错误处理，也许是对参数值进行的一些理智检查，也许可以在更新之前检查存在。使用两个零件名称（schema.table）是开发的最佳实践。