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
当前回答
一个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
其他回答
最新版本的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线程上发表了评论。
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的平台)，那就使用它!
对于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)
试试这个:
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
在不使用任何类型的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
