另一个复杂的 SQL 2000 查询问题

Question

我想在查询中使用两个表，tblEmployee 和 tblEmpPerformance

要求是：

使用 tblEmployee.EmpID、tblEmployee.ManagerID，浏览tblEmpPerformance并查看tblEmpPerformance.SalaryRaise和tblEmpPerformance.CommissionRaise，并相应地更新tblEmployee。

对于 tblEmpPerformance 中的每条记录，只有这两个字段之一具有非零值。

if tblEmployee.EmpID = tblEmpPerformance.EmpID then 
If tblEmpPerformance.Salary > 0 then
    update tblEmployee
    Set CompensationType = 'Salary'
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID   

    update tblEmployee
    Set SalaryRaise = tblEmpPerformance.SalaryRaise
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID 

If tblEmpPerformance.Commission > 0 then
    update tblEmployee
    Set CompensationType = 'Commission'
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID 

    update tblEmployee
    Set CommissionRaise = tblEmpPerformance.CommissionRaise
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID 

如果未找到匹配的 EmpID，则在 tblContractorPerformance 中查找>

if tblEmployee.EmpID = tblContractorPerformance.EmpID then 
    If tblContractorPerformance.Salary > 0 then
    update tblEmployee
    Set CompensationType = 'Salary'
    Where tblEmployee.EmpID = tblContractorPerformance.EmpID 
    AND tblEmployee.ManagerID = tblContractorPerformance.ManagerID 
    ....
    ....
    ....
    ....

Next tblEmployee.EmpID

更新： 哦！！.. 编写此 SQL 查询的最佳方式是什么？存储过程还是游标？ 任何有关如何设计查询的建议也会有很大帮助

Answer 1

据我所知，您可以将其封装到一个查询中：

Update tblEmployee
Set CompensationType = Case
                        When EP.Commission > 0 Then 'Commission'
                        When EP.Salary > 0 Then 'Salary'
                        When CP.Salary > 0 Then 'Salary'
                        Else E.CompensationType -- leave value as is
                        End
    , CommissionRaise = Case
                        When EP.Commission > 0 Then EP.CommissionRaise
                        Else E.CommissionRaise -- leave value as is
                        End
    , SalaryRaise = Case
                        When EP.Commission > 0 Then E.SalaryRaise
                        When EP.Salary > 0 Then EP.SalaryRaise
                        When CP.Salary > 0 Then CP.SalaryRaise
                        Else E.SalaryRaise -- leave value as is
                        End
From tblEmployee As E
    Left Join tblEmployeePerformance As EP
        On EP.EmpID = E.EmpID
            And EP.ManagerId = E.ManagerId
    Left Join tblContractorPerformance As CP
        On CP.EmpID = E.EmpID
            And CP.ManagerId = E.ManagerId

Answer 2

请避免使用光标。考虑基于集合来处理数据。因此，执行您的第一组数据，其中 tblEmpPerformance.Salary > 0 。进行更新，然后对下一组数据执行第二个操作，其中 tblEmpPerformance.Commission > 0 。最后，更新 tblContractorPerformance.Salary > 的所有数据。 0 。

将这些操作放入存储过程中，我相信您会看到比使用游标的等效操作更好的性能。