在SQL中使用Cursor对每个ROW求和 [英] Summing Each ROW using Cursor in Sql

问题描述

嗨盖兹，

hi guyz,

Balance          Month
-1308998.02	1
    -990000	2
 1178835.01	3
 -188835.01	4
          0	5
     794.99	6
  160589.98	7
    -159000	8
     1405.6	9
      13200	10
 1721328.02	11
  436984.49	12

我想将余额与他们各自月份的总和相加.例如，求和后的第1个月和第2个月将是2298998.02，并针对第2个月显示.对于所有

I want to sum the balance with respective of their months. like month 1 and 2 after summing will be 2298998.02 and will be displayed against month 2. same for all

Balance       ance(after summing)        Month
-1308998.02	-1308998.02	1
    -990000	-2298998.02	2
 1178835.01	 3477833.03	3
 -188835.01	-3666668.04	4
          0  	 3666668.04	5
     794.99	 3667463.03	6
  160589.98 	 3828053.01	7
    -159000	-3987053.01	8
     1405.6	-3985647.41	9
      13200	 3998847.41	10
 1721328.02	 5720175.43	11
  436984.49	 6157159.92	12

推荐答案

Don'不要使用游标.
在SQL 2012中，使用窗口功能.在此处查看示例: http://sqlmag .com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1 [

Don''t use a cursor.
In SQL 2012, use windowing functions. See an example here: http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1[^]
In earlier versions, use a CTE (Common Table Expression) and a subquery.

Hope this helps,
Pablo.

您正在寻找的是"Running Total"，CP对此主题有一篇很好的文章搜索 [

What you are looking for is "Running Total" and CP has a nice article on the subject Calculating simple running totals in SQL Server[^]

for more info you can do a google Search[^].

这应该可以解决您的问题，但我不知道为什么我得到了不同的结果！您是否手动计算了它们?

This should solve your problem but I don''t know why I got different result ! Had you computed them manually ?

declare @tbl1 as table(
balance decimal(18,2),
month int)
insert into @tbl1 values (-1308998.02,  1)
insert into @tbl1 values (    -990000,  2)
insert into @tbl1 values ( 1178835.01,  3)
insert into @tbl1 values ( -188835.01,  4)
insert into @tbl1 values (          0,  5)
insert into @tbl1 values (     794.99,  6)
insert into @tbl1 values (  160589.98,  7)
insert into @tbl1 values (    -159000,  8)
insert into @tbl1 values (     1405.6,  9)
insert into @tbl1 values (      13200,  10)
insert into @tbl1 values ( 1721328.02,  11)
insert into @tbl1 values (  436984.49,  12)

select balance, month, (select sum(balance) from @tbl1 where month <= t.month ) [after sum] from @tbl1 t

祝你好运

Good Luck

这篇关于在SQL中使用Cursor对每个ROW求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！