使用Sum和Over运行总计在SQL Server 2012中不起作用

Question

我有一群员工加薪了。单个员工可以多次加薪，每次加薪都是出于不同的原因。一个员工不能因为相同的原因多次加薪，所以EmployeeID和RaiseReason的组合是唯一的。

每次加薪后，我需要保持每个员工的工资总额。

所以我有：

SELECT 
    EmployeeID, CurrentSalary, RaiseAmount, RaiseReason, 
    CurrentSalary + SUM(RaiseAmount) OVER (PARTITION BY EmployeeID, RaiseReason ORDER BY EmployeeID, RaiseReason) AS RunningSalaryTotal
FROM 
    Employees

如果我运行它，我不会得到运行的总数，相反，它会重置每次加薪的总和。

例如：

EmployeeID   CurrentSalary   RaiseAmount   RaiseReason   RunningSalaryTotal
---------------------------------------------------------------------------
     1         100000          1000        Performance        101000
     1         100000          5000        Promotion          105000

第二条记录的运行工资合计应为100000 + 1000 + 5000 = 106000。

如果我只按EmployeeID分区，则会显示每个员工的最终总数。

例如：

EmployeeID   CurrentSalary   RaiseAmount   RaiseReason   RunningSalaryTotal
---------------------------------------------------------------------------
     1         100000          1000        Performance        106000
     1         100000          5000        Promotion          106000

现在它只是一个总数，而不是一个连续的总数。

如何让RunningSalaryTotal为每条记录正确递增？

Answer 1

尝尝这个

SELECT EmployeeID, CurrentSalary, RaiseAmount, RaiseReason
    , CurrentSalary + SUM(RaiseAmount) OVER (PARTITION BY EmployeeID
                                                ORDER BY EmployeeID 
                                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningSalaryTotal
FROM Employees

Answer 2

只需通过以下方式从分区中删除raisereason

SELECT EmployeeID, CurrentSalary, RaiseAmount, RaiseReason, CurrentSalary + SUM(RaiseAmount) OVER (PARTITION BY EmployeeID ORDER BY EmployeeID, RaiseReason) AS RunningSalaryTotal
FROM Employees

Sql code to test