从上一行减去 [英] Subtract from previous row

问题描述

大家好，我正在使用Sql server 2012。我有一张桌子

ie

Column1

32

-153

174

-5



我想要结果表格像

Hello guys,I am using Sql server 2012 . I have a table
i.e
Column1
32
-153
174
-5

I want result table like

Column1 Column2
32        32
-153      -121 
174       53 
-5        48 

...

in Each Row下一行的值加上prev值

ie -153 + 32 = -121

-121 + 174 = 53



我听说Sql server 2012中的Lead和Lag但尚未使用。如果您有任何想法那么请告诉我..

...
in Each Row next row value is added with prev value
i.e -153 + 32 =-121
-121+174=53

I heard about Lead and Lag in Sql server 2012 but not yet used.If you have any idea then let me know..

推荐答案

请看这里： [ ^ ]

试试这个：

Try this:

WITH cte1 AS 
(
    select   column1,
             rownum = row_number() OVER (ORDER BY (SELECT 0))
    from     table1 
)
SELECT column1,
case rownum
  when 1 then column1 else
column1 + 
(
  select sum(column1) from cte1 c2 where c2.rownum < c1.rownum
)
end
FROM    cte1 c1
WHERE   rownum > 0

这里描述的内容称为运行总计。这是一个非常常见的讨论主题，有几种方法。是的，SQL 2012产生了一些支持这种查询的功能。我建议你阅读这篇关于你可能拥有的可能性及其性能考虑因素的文章：

http://sqlperformance.com/2012/07/t-sql-queries/running-totals [ ^ ]

LEAD和LAG不适用于此，因为它们可用于访问您的集合中的NEXT和PREVIOUS行，但您不能使用它们累积，因为您的累积不是集合的一部分（请参阅此文章：http://www.bidn.com/blogs/KathiKellenberger/sql-server/3300/don-t-得知 - 落后学习使用滞后 [ ^ ]）。

What you describe here is called running total. It is a quite common discussion topic and there are several approaches. Yes, SQL 2012 itroduced some feature that are supportung such queries. I suggest you read this extensive article about the possibilities you might have, and their performance considerations:
http://sqlperformance.com/2012/07/t-sql-queries/running-totals[^]
LEAD and LAG are not for that, as they can be used to access the NEXT and PREVIOUS rows in your set, but you can't use them to accumulate, as your cumulation is not part of the set (see this article: http://www.bidn.com/blogs/KathiKellenberger/sql-server/3300/don-t-get-left-behind-learn-to-use-lag[^]).

这篇关于从上一行减去的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！