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
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 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
SQL小提琴示例
输出
| ID | SOMENUMT | SUM |
-----------------------
| 1 | 10 | 10 |
| 2 | 12 | 22 |
| 3 | 3 | 25 |
| 4 | 15 | 40 |
| 5 | 23 | 63 |
编辑:这是一个通用的解决方案，可以在大多数数据库平台上工作。如果有更好的解决方案适用于你的特定平台(例如，gareth的平台)，那就使用它!
让我们先用虚拟数据创建一个表:
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
在上面(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
例如:如果你有一个有两列的表，一列是ID，第二列是number，并且想要找出累积和。
SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T
在不使用任何类型的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
