如何计算累计金额

declare  @t table
(
id int,
SomeNumt int
)


insert into @t
select 1,10
union
select 2,12
union
select 3,3
union
select 4,15
union
select 5,23




select * from @t

上面的选择返回以下内容。

id  SomeNumt
1   10
2   12
3   3
4   15
5   23

我如何得到以下结果:

id  srome   CumSrome
1   10  10
2   12  22
3   3   25
4   15  40
5   23  63
493688 次浏览
小开
最佳答案 
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id

< a href = " http://sqlfiddle.com/ !18/abdce/1" rel="noreferrer">SQL小提琴示例 . 1" rel="noreferrer">SQL小提琴示例 . 1

输出

| ID | SOMENUMT | SUM |
-----------------------
|  1 |       10 |  10 |
|  2 |       12 |  22 |
|  3 |        3 |  25 |
|  4 |       15 |  40 |
|  5 |       23 |  63 |

编辑:这是一个通用的解决方案，将工作在大多数db平台。如果有更好的解决方案适用于你的特定平台(例如，gareth的平台)，那就使用它!

小开

一个CTE版本，只是为了好玩:

;
WITH  abcd
AS ( SELECT id
,SomeNumt
,SomeNumt AS MySum
FROM   @t
WHERE  id = 1
UNION ALL
SELECT t.id
,t.SomeNumt
,t.SomeNumt + a.MySum AS MySum
FROM   @t AS t
JOIN abcd AS a ON a.id = t.id - 1
)
SELECT  *  FROM    abcd
OPTION  ( MAXRECURSION 1000 ) -- limit recursion here, or 0 for no limit.

返回:

id          SomeNumt    MySum
----------- ----------- -----------
1           10          10
2           12          22
3           3           25
4           15          40
5           23          63
小开
在这篇优秀的文章中有一个更快的CTE实现: http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx < / p >

这个线程中的问题可以这样表达: 

    DECLARE @RT INT
SELECT @RT = 0


;
WITH  abcd
AS ( SELECT TOP 100 percent
id
,SomeNumt
,MySum
order by id
)
update abcd
set @RT = MySum = @RT + SomeNumt
output inserted.*

> < /代码

小开

最新版本的SQL Server(2012)允许以下。

SELECT
RowID,
Col1,
SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId

或

SELECT
GroupID,
RowID,
Col1,
SUM(Col1) OVER(PARTITION BY GroupID ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId

这个更快。分区版本在34秒内完成，超过500万行。

感谢Peso，他在另一个回答中提到的SQL Team线程上发表了评论。

小开

让我们先用虚拟数据创建一个表:

Create Table CUMULATIVESUM (id tinyint , SomeValue tinyint)

现在让我们向表中插入一些数据;

Insert Into CUMULATIVESUM
Select 1, 10 union
Select 2, 2  union
Select 3, 6  union
Select 4, 10

这里我在连接同一个表(自连接)

Select c1.ID, c1.SomeValue, c2.SomeValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Order By c1.id Asc

结果:

ID  SomeValue   SomeValue
-------------------------
1   10          10
2   2           10
2   2            2
3   6           10
3   6            2
3   6            6
4   10          10
4   10           2
4   10           6
4   10          10

现在我们把t2的somvalue相加，我们就会得到答案

Select c1.ID, c1.SomeValue, Sum(c2.SomeValue) CumulativeSumValue
From CumulativeSum c1,  CumulativeSum c2
Where c1.id >= c2.ID
Group By c1.ID, c1.SomeValue
Order By c1.id Asc

对于SQL Server 2012及以上版本(性能更好):

Select
c1.ID, c1.SomeValue,
Sum (SomeValue) Over (Order By c1.ID )
From CumulativeSum c1
Order By c1.id Asc

预期的结果:

ID  SomeValue   CumlativeSumValue
---------------------------------
1   10          10
2   2           12
3   6           18
4   10          28


Drop Table CumulativeSum
小开

试试这个

select
t.id,
t.SomeNumt,
sum(t.SomeNumt) Over (Order by t.id asc Rows Between Unbounded Preceding and Current Row) as cum
from
@t t
group by
t.id,
t.SomeNumt
order by
t.id asc;
小开

一旦创建了表-

select
A.id, A.SomeNumt, SUM(B.SomeNumt) as sum
from @t A, @t B where A.id >= B.id
group by A.id, A.SomeNumt


order by A.id
小开 
Select
*,
(Select Sum(SOMENUMT)
From @t S
Where S.id <= M.id)
From @t M
小开

试试这个:

CREATE TABLE #t(
[name] varchar NULL,
[val] [int] NULL,
[ID] [int] NULL
) ON [PRIMARY]


insert into #t (id,name,val) values
(1,'A',10), (2,'B',20), (3,'C',30)


select t1.id, t1.val, SUM(t2.val) as cumSum
from #t t1 inner join #t t2 on t1.id >= t2.id
group by t1.id, t1.val order by t1.id
小开

对于SQL Server 2012以后，它可以很容易:

SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t

因为SUM的ORDER BY子句默认表示窗框的RANGE UNBOUNDED PRECEDING AND CURRENT ROW(“一般备注”在https://msdn.microsoft.com/en-us/library/ms189461.aspx)

小开

回答晚了，但显示了另一种可能性…

使用CROSS APPLY逻辑可以更好地优化Cumulative Sum的生成。

比INNER JOIN &OVER Clause当分析实际的查询计划时…

/* Create table & populate data */
IF OBJECT_ID('tempdb..#TMP') IS NOT NULL
DROP TABLE #TMP


SELECT * INTO #TMP
FROM (
SELECT 1 AS id
UNION
SELECT 2 AS id
UNION
SELECT 3 AS id
UNION
SELECT 4 AS id
UNION
SELECT 5 AS id
) Tab




/* Using CROSS APPLY
Query cost relative to the batch 17%
*/
SELECT   T1.id,
T2.CumSum
FROM     #TMP T1
CROSS APPLY (
SELECT   SUM(T2.id) AS CumSum
FROM     #TMP T2
WHERE    T1.id >= T2.id
) T2


/* Using INNER JOIN
Query cost relative to the batch 46%
*/
SELECT   T1.id,
SUM(T2.id) CumSum
FROM     #TMP T1
INNER JOIN #TMP T2
ON T1.id > = T2.id
GROUP BY T1.id


/* Using OVER clause
Query cost relative to the batch 37%
*/
SELECT   T1.id,
SUM(T1.id) OVER( PARTITION BY id)
FROM     #TMP T1


Output:-
id       CumSum
-------   -------
1         1
2         3
3         6
4         10
5         15
小开

SQL解决方案结合“无界前行和当前行之间的行”和“SUM”做的正是我想要实现的。 非常感谢!< / p >

如果这能帮到谁，这是我的案子。我想在一列中累积+1，每当发现一个maker为“Some maker”(示例)。如果不是，则不增加，但显示之前的增加结果。

这段SQL:

SUM( CASE [rmaker] WHEN 'Some Maker' THEN  1 ELSE 0 END)
OVER
(PARTITION BY UserID ORDER BY UserID,[rrank] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumul_CNT

让我得到这样的东西:

User 1  Rank1   MakerA      0
User 1  Rank2   MakerB      0
User 1  Rank3   Some Maker  1
User 1  Rank4   Some Maker  2
User 1  Rank5   MakerC      2
User 1  Rank6   Some Maker  3
User 2  Rank1   MakerA      0
User 2  Rank2   SomeMaker   1

以上解释:它从0开始计数“some maker”，some maker被找到，我们做+1。对于用户1,MakerC被找到，所以我们不做+1，而是一些制造商的垂直计数被固定为2，直到下一行。 分区是按用户划分的，所以当我们改变用户时，累积计数返回零

我在工作，我不希望这个答案有任何优点，只是说谢谢，并以身作则，以防有人处于同样的情况。我试图结合SUM和PARTITION，但惊人的语法“无界前行和当前行之间的行”完成了任务。

< p >谢谢! Groaker < / p >
小开

在上面(Pre-SQL12)我们看到了这样的例子:-

SELECT
T1.id, SUM(T2.id) AS CumSum
FROM
#TMP T1
JOIN #TMP T2 ON T2.id < = T1.id
GROUP BY
T1.id

更高效的…

SELECT
T1.id, SUM(T2.id) + T1.id AS CumSum
FROM
#TMP T1
JOIN #TMP T2 ON T2.id < T1.id
GROUP BY
T1.id
小开

在不使用任何类型的JOIN的情况下，通过使用follow查询获取一个人的累计工资:

SELECT * , (
SELECT SUM( salary )
FROM  `abc` AS table1
WHERE table1.ID <=  `abc`.ID
AND table1.name =  `abc`.Name
) AS cum
FROM  `abc`
ORDER BY Name
小开

你可以使用这个简单的查询进行累进计算:

select
id
,SomeNumt
,sum(SomeNumt) over(order by id ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as CumSrome
from @t
小开

例如:如果你有一个有两列的表，一列是ID，第二列是number，并且想要找出累积和。

SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T

提交答案