WITH a AS( SELECT '2015-01-01' AS d , 100 AS e UNION ALL SELECT '2015-01-02' AS d, 100 AS e UNION ALL SELECT '2015-01-03' AS d, 100 AS e UNION ALL SELECT '2015-01-04' AS d, 100 AS e UNION ALL SELECT '2015-01-05' AS d, 100 AS e UNION ALL SELECT '2015-01-06' AS d, 100 AS e ) SELECT d , e , SUM(e) OVER (ORDER BY d) FROM a 要2012的版本
数据库什么版本?是否支持sum()over()这种分析函数的写法 如果能的话,使用分析函数效率应该是最高的 select 日期,金额,sum(金额)over(order by 日期) 合计 from T
WITH a AS( SELECT '2015-01-01' AS d, 100 AS e UNION ALL SELECT '2015-01-02' AS d, 100 AS e UNION ALL SELECT '2015-01-03' AS d, 100 AS e UNION ALL SELECT '2015-01-04' AS d, 100 AS e UNION ALL SELECT '2015-01-05' AS d, 100 AS e UNION ALL SELECT '2015-01-06' AS d, 100 AS e ) , b AS ( SELECT ROW_NUMBER() OVER(ORDER BY d) AS rown, d, e FROM a )SELECT *, ( SELECT SUM(e) FROM b AS c WHERE c.rown <= b.rown ) FROM b 这个是之前版本的写法
http://blog.csdn.net/blandwolf/article/details/34837559
SELECT '2015-01-01' AS d , 100 AS e UNION ALL
SELECT '2015-01-02' AS d, 100 AS e UNION ALL
SELECT '2015-01-03' AS d, 100 AS e UNION ALL
SELECT '2015-01-04' AS d, 100 AS e UNION ALL
SELECT '2015-01-05' AS d, 100 AS e UNION ALL
SELECT '2015-01-06' AS d, 100 AS e )
SELECT d , e , SUM(e) OVER (ORDER BY d) FROM a
要2012的版本
如果能的话,使用分析函数效率应该是最高的
select 日期,金额,sum(金额)over(order by 日期) 合计
from T
SELECT '2015-01-01' AS d,
100 AS e UNION ALL
SELECT '2015-01-02' AS d,
100 AS e UNION ALL
SELECT '2015-01-03' AS d,
100 AS e UNION ALL
SELECT '2015-01-04' AS d,
100 AS e UNION ALL
SELECT '2015-01-05' AS d,
100 AS e UNION ALL
SELECT '2015-01-06' AS d,
100 AS e
) ,
b AS (
SELECT ROW_NUMBER() OVER(ORDER BY d) AS rown,
d,
e
FROM a
)SELECT *,
(
SELECT SUM(e)
FROM b AS c
WHERE c.rown <= b.rown
)
FROM b
这个是之前版本的写法