sql - 如何确定连续契约(Contract)行中员工工资的增加？

Question

我的查询有问题:

我的表是这样存储数据的

ContractID | Staff_ID | EffectDate  | End Date   | Salary | active
-------------------------------------------------------------------------
1          | 1        | 2013-01-01  | 2013-12-30 | 100    | 0
2          | 1        | 2014-01-01  | 2014-12-30 | 150    | 0
3          | 1        | 2015-01-01  | 2015-12-30 | 200    | 1
4          | 2        | 2014-05-01  | 2015-04-30 | 500    | 0
5          | 2        | 2015-05-01  | 2016-04-30 | 700    | 1

我想编写如下查询:

ContractID | Staff_ID | EffectDate  | End Date   | Salary | Increase
-------------------------------------------------------------------------
1          | 1        | 2013-01-01  | 2013-12-30 | 100    | 0
2          | 1        | 2014-01-01  | 2014-12-30 | 150    | 50
3          | 1        | 2015-01-01  | 2015-12-30 | 200    | 50
4          | 2        | 2014-05-01  | 2015-04-30 | 500    | 0
5          | 2        | 2015-05-01  | 2016-04-30 | 700    | 200  
-------------------------------------------------------------------------

涨幅栏为当前合约减去上期合约

我用的是sql server 2008 R2

Answer 1

很遗憾，2008R2 无法访问 LAG , 但您可以模拟在当前行 ( prev ) 的范围内获取前一行 ( cur ) 的效果, 使用 RANKing 和自连接到前一个排名行, 在 Staff_ID 的同一分区中) :

With CTE AS 
(
    SELECT [ContractID], [Staff_ID], [EffectDate], [End Date], [Salary],[active], 
    ROW_NUMBER() OVER (Partition BY Staff_ID ORDER BY ContractID) AS Rnk
    FROM Table1
)
SELECT cur.[ContractID], cur.[Staff_ID], cur.[EffectDate], cur.[End Date],
    cur.[Salary], cur.Rnk,
    CASE WHEN (cur.Rnk = 1) THEN 0 -- i.e. baseline salary
         ELSE cur.Salary - prev.Salary END AS Increase
FROM CTE cur
    LEFT OUTER JOIN CTE prev
    ON cur.[Staff_ID] = prev.Staff_ID and cur.Rnk - 1 = prev.Rnk;

(如果 ContractId 总是完美递增，我们就不需要 ROW_NUMBER 并且可以加入递增 ContractId，我不想做这个假设)。

SqlFiddle here

编辑

如果您有 Sql 2012 及更高版本，LEAD and LAG Analytic Functions使这种查询更简单:

SELECT [ContractID], [Staff_ID], [EffectDate], [End Date], [Salary], 
Salary - LAG(Salary, 1, Salary) OVER (Partition BY Staff_ID ORDER BY ContractID) AS Incr
FROM Table1

Updated SqlFiddle

这里的一个技巧是我们正在计算工资的增量增量，因此对于第一个员工契约(Contract)，我们需要返回当前工资，以便 Salary - Salary = 0第一次增加。