blocks|key|1135782|text|一个CTE版本，只是为了好玩：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1135783|;
WITH++abcd
++++++++AS+(+SELECT+id
+++++++++++++++++++,SomeNumt
+++++++++++++++++++,SomeNumt+AS+MySum
+++++++++++++FROM+++@t
+++++++++++++WHERE++id+=+1
+++++++++++++UNION+ALL
+++++++++++++SELECT+t.id
+++++++++++++++++++,t.SomeNumt
+++++++++++++++++++,t.SomeNumt+%2B+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.|code-block|syntax|javascript|1135784|返回：|1135785|id++++++++++SomeNumt++++MySum
-----------+-----------+-----------
1+++++++++++10++++++++++10
2+++++++++++12++++++++++22
3+++++++++++3+++++++++++25
4+++++++++++15++++++++++40
5+++++++++++23++++++++++63|1135786|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

A CTE version, just for fun:

<pre><code>;
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.
</code></pre>

Returns:

<pre><code>id SomeNumt MySum
----------- ----------- -----------
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
</code></pre>

blocks|key|1351114|text|在这篇优秀的文章中有一个更快的CTE实现：http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1351115|这个线程中的问题可以这样表达：|1351116|++++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+%2B+SomeNumt
++++++output+inserted.*|code-block|syntax|javascript|1351117|​|1351118|entityMap|0|LINK|mutability|MUTABLE|url|http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx^0|L|2M|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

There is a much faster CTE implementation available in this excellent post:
<a href="http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx" rel="nofollow">http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx</a>

The problem in this thread can be expressed like this:
 <code>

<pre><code> 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.*
</code></pre>

</code>

blocks|key|1351155|text|SQL+Server的最新版本(2012)允许执行以下操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351156|SELECT+
++++RowID,+
++++Col1,
++++SUM(Col1)+OVER(ORDER+BY+RowId+ROWS+BETWEEN+UNBOUNDED+PRECEDING+AND+CURRENT+ROW)+AS+Col2
FROM+tablehh
ORDER+BY+RowId|code-block|syntax|javascript|1351157|或|1351158|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|1351159|这甚至更快。对我来说，分区版本在34秒内就完成了超过500万行。|1351160|感谢Peso，他在另一个答案中提到的SQL团队线程上发表了评论。|1351161|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

The latest version of SQL Server (2012) permits the following.

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

or

<pre><code>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
</code></pre>

This is even faster. Partitioned version completes in 34 seconds over 5 million rows for me.

Thanks to Peso, who commented on the SQL Team thread referred to in another answer.

blocks|key|1351191|text|让我们首先创建一个包含虚拟数据的表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351192|Create+Table+CUMULATIVESUM+(id+tinyint+,+SomeValue+tinyint)|code-block|syntax|javascript|1351193|现在，让我们向表中插入一些数据；|1351194|Insert+Into+CUMULATIVESUM
++++Select+1,+10+union+
++++Select+2,+2++union
++++Select+3,+6++union
++++Select+4,+10+|1351195|这里我正在连接同一个表(自连接)|1351196|Select+c1.ID,+c1.SomeValue,+c2.SomeValue
From+CumulativeSum+c1,+CumulativeSum+c2
Where+c1.id+>=+c2.ID
Order+By+c1.id+Asc|1351197|结果：|1351198|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|1351199|现在，我们只需将t2的索姆值相加，我们就会得到答案：|1351200|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|1351201|对于SQL+Server+2012及更高版本(更好的性能)：|1351202|Select+
++++c1.ID,+c1.SomeValue,+
++++Sum+(SomeValue)+Over+(Order+By+c1.ID+)
From+CumulativeSum+c1
Order+By+c1.id+Asc|1351203|期望的结果：|1351204|ID++SomeValue+++CumlativeSumValue
---------------------------------
1+++10++++++++++10
2+++2+++++++++++12
3+++6+++++++++++18
4+++10++++++++++28

Drop+Table+CumulativeSum|1351205|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|18|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|1I|8|@]|9|@]|A|$]]|$1|12|3|13|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|14|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|15|$]]

Let's first create a table with dummy data:
<pre><code>Create Table CUMULATIVESUM (id tinyint , SomeValue tinyint)
</code></pre>
Now let's insert some data into the table;
<pre><code>Insert Into CUMULATIVESUM
 Select 1, 10 union 
 Select 2, 2 union
 Select 3, 6 union
 Select 4, 10 
</code></pre>
Here I am joining same table (self joining)
<pre><code>Select c1.ID, c1.SomeValue, c2.SomeValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id &gt;= c2.ID
Order By c1.id Asc
</code></pre>
Result:
<pre><code>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
</code></pre>
Here we go now just sum the Somevalue of t2 and we`ll get the answer:
<pre><code>Select c1.ID, c1.SomeValue, Sum(c2.SomeValue) CumulativeSumValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id &gt;= c2.ID
Group By c1.ID, c1.SomeValue
Order By c1.id Asc
</code></pre>
For SQL Server 2012 and above (much better performance):
<pre><code>Select 
 c1.ID, c1.SomeValue, 
 Sum (SomeValue) Over (Order By c1.ID )
From CumulativeSum c1
Order By c1.id Asc
</code></pre>
Desired result:
<pre><code>ID SomeValue CumlativeSumValue
---------------------------------
1 10 10
2 2 12
3 6 18
4 10 28

Drop Table CumulativeSum
</code></pre>

blocks|key|1135890|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1135891|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;|code-block|syntax|javascript|1135892|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this

<pre><code>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;
</code></pre>

blocks|key|1135901|text|一旦创建了表-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1135902|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|code-block|syntax|javascript|1135903|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Once the table is created - 

<pre><code>select 
 A.id, A.SomeNumt, SUM(B.SomeNumt) as sum
 from @t A, @t B where A.id &gt;= B.id
 group by A.id, A.SomeNumt

order by A.id
</code></pre>

blocks|key|1135915|text|Select+
++++*,+
++++(Select+Sum(SOMENUMT)+
+++++From+@t+S+
+++++Where+S.id+<=+M.id)
From+@t+M|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1135916|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Select 
 *, 
 (Select Sum(SOMENUMT) 
 From @t S 
 Where S.id &lt;= M.id)
From @t M
</code></pre>

blocks|key|1351275|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351276|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|code-block|syntax|javascript|1351277|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>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 &gt;= t2.id
 group by t1.id, t1.val order by t1.id
</code></pre>

blocks|key|1351313|text|对于SQL+Server+2012及更高版本，这可能很简单：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351314|SELECT+id,+SomeNumt,+sum(SomeNumt)+OVER+(ORDER+BY+id)+as+CumSrome+FROM+@t|code-block|syntax|javascript|1351315|因为默认情况下，SUM的ORDER+BY子句表示窗口框架的RANGE+UNBOUNDED+PRECEDING+AND+CURRENT+ROW+(https://msdn.microsoft.com/en-us/library/ms189461.aspx中的“一般备注”)|offset|length|style|CODE|1351316|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms189461.aspx^0|0|0|8|3|C|8|T|15|20|1I|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]|$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@$I|13|J|14|1|15]]|A|$]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

For SQL Server 2012 onwards it could be easy:

<pre><code>SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t
</code></pre>

because <code>ORDER BY</code> clause for <code>SUM</code> by default means <code>RANGE UNBOUNDED PRECEDING AND CURRENT ROW</code> for window frame ("General Remarks" at <a href="https://msdn.microsoft.com/en-us/library/ms189461.aspx" rel="noreferrer">https://msdn.microsoft.com/en-us/library/ms189461.aspx</a>)

blocks|key|1351354|text|回答晚了，但又多了一种可能性...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351355|使用CROSS+APPLY逻辑可以更优化累积和生成。|offset|length|style|CODE|1351356|在分析实际的查询计划时，它比INNER+JOIN+&+OVER+Clause更好……|1351357|/*+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%25
*/++++
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%25
*/
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%25
*/
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|code-block|syntax|javascript|1351358|entityMap^0|0|2|B|0|E|A|R|B|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@$D|V|E|W|F|G]|$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|P|$]]

Late answer but showing one more possibility...

Cumulative Sum generation can be more optimized with the <code>CROSS APPLY</code> logic.

Works better than the <code>INNER JOIN</code> &amp; <code>OVER Clause</code> when analyzed the actual query plan ...

<pre><code>/* Create table &amp; 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 &gt;= 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 &gt; = 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
</code></pre>

blocks|key|1136061|text|SQL解决方案结合了“无限前行和当前行之间的行”和"SUM“，这正是我想要实现的目标。非常感谢!|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1136062|如果它能帮助任何人，这就是我的案例。我想在一列中累加%2B1，只要找到一个“某个制造商”(例如)。如果不是，则不递增，但显示先前递增结果。|1136063|所以这段SQL：|1136064|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|code-block|syntax|javascript|1136065|允许我得到这样的东西：|1136066|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++|1136067|上面的解释:它从0开始计算“一些maker”，找到了一些Maker，我们做了%2B1。对于用户1，找到了MakerC，所以我们不做%2B1，而是将一些Maker的垂直计数固定为2，直到下一行。分区是按用户划分的，因此当我们更改用户时，累计计数将恢复为零。|1136068|我在工作，我不想在这个答案上有任何优点，只要说谢谢，并展示我的例子，以防有人遇到同样的情况。我试图组合SUM和PARTITION，但是令人惊叹的语法“在无限的前行和当前行之间的行”完成了任务。|1136069|谢谢!Groaker|1136070|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

The SQL solution wich combines "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" and "SUM" did exactly what i wanted to achieve.
Thank you so much!

If it can help anyone, here was my case. I wanted to cumulate +1 in a column whenever a maker is found as "Some Maker" (example). If not, no increment but show previous increment result.

So this piece of SQL:

<pre><code>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
</code></pre>

Allowed me to get something like this:

<pre><code>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 
</code></pre>

Explanation of above: It starts the count of "some maker" with 0, Some Maker is found and we do +1. For User 1, MakerC is found so we dont do +1 but instead vertical count of Some Maker is stuck to 2 until next row.
Partitioning is by User so when we change user, cumulative count is back to zero.

I am at work, I dont want any merit on this answer, just say thank you and show my example in case someone is in the same situation. I was trying to combine SUM and PARTITION but the amazing syntax "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" completed the task. 

Thanks!
Groaker

blocks|key|1136115|text|上面(在SQL12之前)我们看到这样的例子：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1136116|SELECT
++++T1.id,+SUM(T2.id)+AS+CumSum
FROM+
++++#TMP+T1
++++JOIN+#TMP+T2+ON+T2.id+<+=+T1.id
GROUP+BY
++++T1.id|code-block|syntax|javascript|1136117|更高效..。|1136118|SELECT
++++T1.id,+SUM(T2.id)+%2B+T1.id+AS+CumSum
FROM+
++++#TMP+T1
++++JOIN+#TMP+T2+ON+T2.id+<+T1.id
GROUP+BY
++++T1.id|1136119|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Above (Pre-SQL12) we see examples like this:-

<pre><code>SELECT
 T1.id, SUM(T2.id) AS CumSum
FROM 
 #TMP T1
 JOIN #TMP T2 ON T2.id &lt; = T1.id
GROUP BY
 T1.id
</code></pre>

More efficient...

<pre><code>SELECT
 T1.id, SUM(T2.id) + T1.id AS CumSum
FROM 
 #TMP T1
 JOIN #TMP T2 ON T2.id &lt; T1.id
GROUP BY
 T1.id
</code></pre>

blocks|key|1136144|text|在不使用任何类型的加入累计薪资的情况下，使用以下查询获取人员：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1136145|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|code-block|syntax|javascript|1136146|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Without using any type of JOIN cumulative salary for a person fetch by using follow query:

<pre><code>SELECT * , (
 SELECT SUM( salary ) 
 FROM `abc` AS table1
 WHERE table1.ID &lt;= `abc`.ID
 AND table1.name = `abc`.Name
) AS cum
FROM `abc` 
ORDER BY Name
</code></pre>

blocks|key|1351567|text|您可以使用这个简单的查询进行渐进式计算：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351568|select+
+++id
++,SomeNumt
++,sum(SomeNumt)+over(order+by+id+ROWS+between+UNBOUNDED+PRECEDING+and+CURRENT+ROW)+as+CumSrome
from+@t|code-block|syntax|javascript|1351569|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use this simple query for progressive calculation :

<pre><code>select 
 id
 ,SomeNumt
 ,sum(SomeNumt) over(order by id ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as CumSrome
from @t
</code></pre>

blocks|key|1351605|text|对于Ex:如果您有一个包含两列的表，其中一列是ID，第二列是number，并想要找出累积总和。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351606|SELECT+ID,Number,SUM(Number)OVER(ORDER+BY+ID)+FROM+T|code-block|syntax|javascript|1351607|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For Ex: IF you have a table with two columns one is ID and second is number and wants to find out the cumulative sum.

<pre><code>SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T
</code></pre>

<pre><code>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
</code></pre>

the above select returns me the following.

<pre><code>id SomeNumt
1 10
2 12
3 3
4 15
5 23
</code></pre>

How do I get the following:

<pre><code>id srome CumSrome
1 10 10
2 12 22
3 3 25
4 15 40
5 23 63
</code></pre>

How to get cumulative sum

SQL Server

declare  @t table    (        id int,        SomeNumt int    )insert into @tselect 1,10unionselect 2,12unionselect 3,3unionselect 4,15unionselect 5,23select * f...

问如何获得累计和
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获得累计和EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获得累计和
EN