对一个字段中记录的算术运算 [英] An arithmetic operation on the records in one field
问题描述
Hello all
我有问题
我想在下图中工作...
在sql server中查询。
感谢您对我的帮助。
http://www14.0zz0.com/2014/05/16/ 09 / 845618102.jpg [
对于那些不愿意或不能的人,请按照提供的链接,图像基本上显示以下内容
RowNumber Column1 Column2(如何计算)
1 614 614固定价值
2 187- 427 = 614-187
3 5- 422 = 427-5
4 21- 401 = 422-21
5 8-393 = 401-8
6 30- 363 = 393-30
换句话说OP想要根据前一行计算一个数字。
我的意思是:
此查询返回3列。
第1行中的第1列是固定值(614)并将其放入第2列中的第1行然后
第2行第2行= 614 -187 = 427
第2行第3行= 427 -5 = 422
....
对不起我的英文简单。
感谢所有
测试它:
DECLARE @ tmp 表(RowNo INT ,Col1 INT )
INSERT INTO @ tmp (RowNo,Col1)
< span class =code-keyword> VALUES ( 1 , 614 ),(< span class =code-digit> 2 , - 187),( 3 , - 5),
( 4 , - 21),( 5 , - 8),( 6 , - 30)
SELECT t1.RowNo,t1.Col1,SUM(t2.Col1) AS CurrValue
FROM @ tmp AS t1 INNER JOIN @ tmp AS t2 ON t1.RowNo> = t2.RowNo
GROUP BY t1.RowNo,t1.Col1
ORDER BY t1.RowNo
结果:
RowNo Col1 CurrVal
1 614 614
2 -187 427
3 -5 422
4 -21 401
5 -8 393
6 -30 363
Hello all
I have a problem
I would like to work as in the picture below ...
in sql server Query.
Thanks for your help to me.
http://www14.0zz0.com/2014/05/16/09/845618102.jpg[
[Edit CHill60]
For those not willing, or unable to, follow the link provided the image essentially shows the following
RowNumber Column1 Column2 (How calculated) 1 614 614 Fixed value 2 187- 427 = 614-187 3 5- 422 = 427-5 4 21- 401 = 422-21 5 8- 393 = 401-8 6 30- 363 = 393-30
In other words the OP wants to calculate a figure based on the previous row.
What I mean is:
This query returns 3 columns.
Column1 in row 1 is fixed value (614) and put it to the row 1 in column2 and then
Row 2 in column2 = 614 -187=427
Row 3 in column2 =427 -5=422
....
I'm sorry for my English simple.
Thanks for All
Test it:
DECLARE @tmp TABLE (RowNo INT, Col1 INT) INSERT INTO @tmp (RowNo, Col1) VALUES(1, 614), (2, -187), (3, -5), (4, -21), (5, -8), (6, -30) SELECT t1.RowNo, t1.Col1, SUM(t2.Col1) AS CurrValue FROM @tmp AS t1 INNER JOIN @tmp AS t2 ON t1.RowNo >= t2.RowNo GROUP BY t1.RowNo, t1.Col1 ORDER BY t1.RowNo
Result:
RowNo Col1 CurrVal 1 614 614 2 -187 427 3 -5 422 4 -21 401 5 -8 393 6 -30 363
这篇关于对一个字段中记录的算术运算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!