sql server 计算累计

[Oliver5914]

sql server 计算数量累计
下面第一个图片是数据库直接查询出来的，第二个图片是希望得到的结果， 系统在查询的列种增加一列是 num字段的累计 ，按照时间排序



测试sql

create table temp_a
(
num int ,
createdate datetime 
)

insert into temp_a
select -1,'2018-01-20 00:19:31.000'
union select -1,'2018-01-13 00:19:46.000'
union select -1,'2018-01-06 00:17:03.000'
union select 4,'2017-12-30 17:15:13.000'
union select -1,'2017-12-30 01:01:53.000'
union select 4,'2017-12-27 16:04:30.000'
union select -1,'2017-12-23 00:24:03.000'
union select -1,'2017-12-16 00:15:02.000'
union select 4,'2017-12-15 16:25:02.000'
union select 4,'2017-12-15 16:14:37.000'
union select 2,'2017-12-13 12:34:34.807'


select * from  temp_a order by createdate desc

[zjcxc]

select *, SUM(num)OVER(ORDER BY createdate desc) from temp_a order by createdate desc

[二月十六]

SELECT  ( SELECT    SUM(num)
          FROM      temp_a b
          WHERE     b.createdate <= a.createdate
        ) ,
        a.*
FROM    temp_a a
ORDER BY a.createdate DESC

[Oliver5914]

引用 1 楼 zjcxc 的回复:

select *, SUM(num)OVER(ORDER BY createdate desc) from temp_a order by createdate desc

有语法错误

[Oliver5914]

引用 2 楼 sinat_28984567 的回复:

SELECT  ( SELECT    SUM(num)
          FROM      temp_a b
          WHERE     b.createdate <= a.createdate
        ) ,
        a.*
FROM    temp_a a
ORDER BY a.createdate DESC

可以的，多谢

[zjcxc]

引用 3 楼 a591402213 的回复:

[quote=引用 1 楼 zjcxc 的回复:] select *, SUM(num)OVER(ORDER BY createdate desc) from temp_a order by createdate desc

有语法错误[/quote] 如果你用的是 sql server，说明你的版本不够

[Oliver5914]

引用 5 楼 zjcxc 的回复:

[quote=引用 3 楼 a591402213 的回复:] [quote=引用 1 楼 zjcxc 的回复:] select *, SUM(num)OVER(ORDER BY createdate desc) from temp_a order by createdate desc

有语法错误[/quote] 如果你用的是 sql server，说明你的版本不够[/quote] 版本是2008 r2

[五维思考]

SELECT SUM(num)OVER(ORDER BY createdate),* FROM temp_a ORDER BY createdate DESC

[中国风]

或用CROSS APPLY e.g.

SELECT a.*
     , b.num2
FROM temp_a AS a
     CROSS APPLY
(SELECT SUM(num) FROM temp_a WHERE createdate<=a.createdate) AS b(num2)
ORDER BY createdate DESC;

[中国风]

SQL2008可用 e.g.

SELECT *
     ,(SELECT SUM(num)  FROM temp_a WHERE createdate<=a.createdate)
FROM temp_a AS a
ORDER BY createdate DESC;