求和并结转值 [英] sum and carry forward values

问题描述

我想使用SUM或任何其他函数来求和一系列
记录如下例所示.

我有这张桌子T1

Col1 Col2
1 20
2 10
1 10
2 5
1 20
3 10

并希望从查询中获得此结果

Col1 Col2 Col3
1 20 20
1 10 30
1 20 50
2 10 10
2 5 15
3 10 10

Col3列应结转Col2中的值和求和值

Hi,
I would like to use SUM or any other function to sum a series of
records as in the following example.

I have this table T1

Col1 Col2
1 20
2 10
1 10
2 5
1 20
3 10

and would like to obtain this result from a query

Col1 Col2 Col3
1 20 20
1 10 30
1 20 50
2 10 10
2 5 15
3 10 10

Column Col3 should carry forward and sum values from Col2

推荐答案

试试这个...

Hi,

Try this...

DECLARE @TblVariable Table
(
  col1 INT,
  col2 INT
);

INSERT INTO @TblVariable VALUES
(1,20),
(2,10),
(1,10),
(2,5),
(1,20),
(3,10);

With tblCTE as
(
Select Row_Number() OVER (Order by col1) as RowNumber,col1,col2 from @TblVariable
)

SELECT Col1,Col2,
( SELECT SUM(Col2) FROM tblCTE T2 WHERE T2.RowNumber <= T1.RowNumber 
  AND T1.Col1 = T2.Col1 GROUP BY Col1
) AS Col3 FROM tblCTE T1 GROUP BY Col1,Col2,RowNumber
ORDER BY Col1, RowNumber

谢谢.

Thank you..

请参阅以下链接.
可能会对您有帮助.

查询SQL Server中的运行总计 [查询SQL Server中的运行总计 [

Hi,

See the below links.
It might be help you.

Query for running total in SQL Server[^]

Query for running total in SQL Server[^]

尝试使用相关的子查询.

Try using a correlated subquery.

select
a.Col1, a.Col2, 
(
select sum(b.Col2)
from t as b
where b.Col1 = a.Col1
) as col3 
from 
t as a;
GO

您也可以使用自我加入来做到这一点:

You can also do it with a self-join:

select a.Col1,a.Col2,sum(b.Col2) as Col3
from T1 as a
inner join T1 as b on b.Col1=a.Col1 
group by a.Col1,a.Col2

但我认为性能会稍差一些(取决于其他过滤条件).

but I supppose the performance will be slightly worse (depends on additional filter criteria).

这篇关于求和并结转值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！