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
当前回答
在不使用任何类型的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
其他回答
在上面(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
例如:如果你有一个有两列的表，一列是ID，第二列是number，并且想要找出累积和。
SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T
对于SQL Server 2012以后，它可以很容易:
SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t
因为SUM的ORDER BY子句默认表示窗口框的前一行和当前行范围为无界(“一般备注”在https://msdn.microsoft.com/en-us/library/ms189461.aspx)
在这篇优秀的文章中有一个更快的CTE实现: http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx
这个线程中的问题可以这样表达:
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.*
