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
当前回答
一旦创建了表-
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
其他回答
让我们先用虚拟数据创建一个表:
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
SQL解决方案结合“无界前行和当前行之间的行”和“和”做的正是我想要实现的。 非常感谢!
如果这能帮到谁，这是我的案子。我想在一列中累积+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，但惊人的语法“无界前行和当前行之间的行”完成了任务。
谢谢! Groaker
最新版本的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
or
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线程上发表了评论。
回答晚了，但显示了另一种可能性…
使用CROSS APPLY逻辑可以更好地优化累积和生成。
在分析实际的查询计划时，比INNER JOIN & OVER子句更好…
/* 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
在不使用任何类型的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
