我的查询有问题:
我的表存储数据是这样的
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
最佳答案
不幸的是，2008R2 无法访问 LAG ，但是您可以模拟在当前行 ( prev ) 范围内获取前一行 ( cur ) 的效果，在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 并且可以加入递增 ContractIds，我不想做出这个假设)。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 。关于sql - 如何从连续的契约(Contract)行确定员工工资的增加？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30431339/