SQL Server反向运行合计

Question

我有一张桌子：

id  ID  Date    Cumulative
1   x   Jan-10  10
3   x   Feb-10  40
7   x   Apr-10  60
9   x   May-10  100
2   y   Jan-10  20
6   y   Mar-10  40
8   y   Apr-10  60
10  y   May-10  100

我需要将MS SQL Server中的"Cumulative“查询反向，如下所示

id  ID  Date    Cumulative  Reversed
1   x   Jan-10  10  10
3   x   Feb-10  40  30
7   x   Apr-10  60  20
9   x   May-10  100 40
2   y   Jan-10  20  20
6   y   Mar-10  40  20
8   y   Apr-10  60  20
10  y   May-10  100 40

注意:查询适用于SQL Server 2012

Answer 1

您可以使用lag获取前一行中的值，然后从当前行的值中减去该值以获得反转的值。

select t.*, cumulative - coalesce(lag(cumulative) over(partition by id order by date),0) as reversed
from tablename t

来自@戈登·林诺夫的评论。您可以使用lag(cumulative,1,0)而不是coalesce。

select t.*, cumulative-lag(cumulative,1,0) over(partition by id order by date) as reversed
from tablename t

Answer 2

对于以下使用递归CTE的Sql server 2012

declare @t table(id int,IDs varchar(20),Dates varchar(20),Cumulative int)
insert into @t values
(1,'x','Jan-10',  10)
,(3,'x','Feb-10',  40)
,(7,'x','Apr-10',  60)
,(9,'x','May-10', 100)
,(2,'y','Jan-10',  20)
,(6,'y','Mar-10',  40)
,(8,'y','Apr-10',  60)
,(10,'y','May-10',100)

;With CTE as
(select *,row_number()over(partition by ids order by id)rn 
from @t
)
,CTE1 as
(select id,ids,dates, Cumulative,rn,Cumulative Reversed 
from cte where rn=1
union all
select c.id,c.ids,c.Dates,c.Cumulative,c.rn 
,c.Cumulative-c1.Cumulative
from cte c
inner join cte c1 on c.ids=c1.ids
where c.rn=c1.rn+1
)
select * from cte1