将列总和到 SQL 中的当前行?
我正在尝试将一列汇总到当前行(在 SQL Server 中).我该怎么做?
I'm trying to sum a column up to the current row (in SQL Server). How do I do this?
select t1.CounterTime,
t1.StartTime,
t1.EndTime,
isNull(t1.value, 0) as value1,
-- How do I make Total1 the sum of t1.value over all previous rows?
sum( isNull(t1.value, 0) ) over (partition by t1.CounterTime order by t1.CounterTime) as Total1
from SomeTable t1
order by t1.CounterTime
但是我弄错了 partition by
...
╔═══╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════╦════════╗
║ ║ CounterTime ║ StartTime ║ EndTime ║ value1 ║ Total1 ║
╠═══╬═════════════════════════╬═════════════════════════╬═════════════════════════╬════════╬════════╣
║ 1 ║ 2015-03-17 12:00:00.000 ║ 2015-03-17 00:00:00.000 ║ 2015-03-18 00:00:00.000 ║ 0 ║ 0 ║
║ 2 ║ 2015-03-18 12:00:00.000 ║ 2015-03-18 00:00:00.000 ║ 2015-03-19 00:00:00.000 ║ 0 ║ 0 ║
║ 3 ║ 2015-03-19 12:00:00.000 ║ 2015-03-19 00:00:00.000 ║ 2015-03-20 00:00:00.000 ║ 422 ║ 422 ║
║ 4 ║ 2015-03-20 12:00:00.000 ║ 2015-03-20 00:00:00.000 ║ 2015-03-21 00:00:00.000 ║ 989 ║ 989 ║
║ 5 ║ 2015-03-21 12:00:00.000 ║ 2015-03-21 00:00:00.000 ║ 2015-03-22 00:00:00.000 ║ 1162 ║ 1162 ║
║ 6 ║ 2015-03-22 12:00:00.000 ║ 2015-03-22 00:00:00.000 ║ 2015-03-23 00:00:00.000 ║ 524 ║ 524 ║
╚═══╩═════════════════════════╩═════════════════════════╩═════════════════════════╩════════╩════════╝
应该是:
╔════════╗
║ Total1 ║
╠════════╣
║ 0 ║
║ 0 ║
║ 422 ║
║ 1411 ║
║ 2573 ║
║ 3097 ║
╚════════╝
推荐答案
试试这个
删除partition by"只使用order by"
Try this
Remove the "partition by" use only "order by"
select t1.CounterTime,
t1.StartTime,
t1.EndTime,
isNull(t1.value, 0) as value1,
-- How do I make Total1 the sum of t1.value over all previous rows?
sum( isNull(t1.value, 0) ) over (order by t1.CounterTime) as Total1
from SomeTable t1
order by t1.CounterTime
相关文章