【求助】SQL 累计求和问题

灵雨飘零 2013-09-29 10:36:38
想实现如下累计求和,求指点:

时间 值
20120101 200
20121121 212
20120104 100
20120411 210
20120501 100
20120401 200
20120104 400

实现如下效果:(只考虑时间排序,不考虑值排序)
时间 值 累计值
20120101 200 200
20120104 100 300
20120104 400 700
20120401 200 900
20120411 210 1110
20120501 100 1210
20121121 212 1422

请问SQL语句如何写???




...全文
536 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
LongRui888 2013-10-10
  • 打赏
  • 举报
回复
引用 14 楼 hunkwangshz 的回复:
SQL2012 with t as ( select 1 as col,timestr,val from myTable1 ) SELECT timestr,val, SUM(val) OVER(PARTITION BY col ORDER BY timestr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal FROM t
随着版本的进化,有了更多的方法来实现,而且更加简洁,希望sql server能支持更多的开窗函数,再增多30个开窗函数,达到oracle的功能。
LongRui888 2013-10-10
  • 打赏
  • 举报
回复
方法很多:



WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
),

aa
as
(
select a.*,
       ROW_NUMBER() over(order by tim) as rownum
from a
)

select a1.tim,
       sum(a2.val) as cume_val
from aa a1
inner join aa a2
        on a1.rownum >= a2.rownum
group by a1.tim,
         a1.rownumtim	cume_val

/*
20120101	200
20120104	300
20120104	700
20120401	900
20120411	1110
20120501	1210
20121121	1422
*/
奔四在望 2013-10-10
  • 打赏
  • 举报
回复
学习了,很好。。
唐诗三百首 2013-10-10
  • 打赏
  • 举报
回复
引用 3 楼 kingboy2008 的回复:
参考答案:
SELECT T1.A_TIME,T1.A_VALUE,(SELECT SUM(T2.A_VALUE) FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T2 WHERE T2.ROW<=T1.ROW ) AS LJ
FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T1

应该可以简化如下,

with t as
(SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW 
 FROM TEST T)
SELECT T1.A_TIME,T1.A_VALUE,
      (SELECT SUM(T2.A_VALUE) FROM t T2 WHERE T2.ROW<=T1.ROW) AS LJ
FROM t T1
hunkwangshz 2013-10-10
  • 打赏
  • 举报
回复
SQL2012 with t as ( select 1 as col,timestr,val from myTable1 ) SELECT timestr,val, SUM(val) OVER(PARTITION BY col ORDER BY timestr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal FROM t
雾岛心情 2013-10-07
  • 打赏
  • 举报
回复
都特码高手啊
Cloud_Hero 2013-10-01
  • 打赏
  • 举报
回复
楼主的意思,是逐行插入数据,同时新增一列作为累计值? 还是已经存在一张表,直接输出到查询?
ai_li7758521 2013-09-30
  • 打赏
  • 举报
回复
引用 7 楼 ai_li7758521 的回复:
WITH TB AS ( SELECT 20120101 AS [Time],200 AS [Value] UNION ALL SELECT 20121121, 212 UNION ALL SELECT 20120104 , 100 UNION ALL SELECT 20120411 , 210 UNION ALL SELECT 20120501 , 100 UNION ALL SELECT 20120401 , 200 UNION ALL SELECT 20120104 , 400 ) ...
有误, WITH T AS ( SELECT 20120101 AS [Time],200 AS [Value] UNION ALL SELECT 20121121, 212 UNION ALL SELECT 20120104 , 100 UNION ALL SELECT 20120411 , 210 UNION ALL SELECT 20120501 , 100 UNION ALL SELECT 20120401 , 200 UNION ALL SELECT 20120104 , 400 ),TB AS ( SELECT *,rn=ROW_NUMBER() OVER(ORDER BY [Time]) FROM T )
SELECT a.[Time],a.[Value],SUM(b.[Value]) AS [SUM] 
FROM TB a JOIN TB b ON a.rn>=b.rn
GROUP BY a.[Time],a.[Value]
ORDER BY a.[Time]

--Time	Value	SUM
--20120101	200	200
--20120104	100	300
--20120104	400	700
--20120401	200	900
--20120411	210	1110
--20120501	100	1210
--20121121	212	1422
ai_li7758521 2013-09-30
  • 打赏
  • 举报
回复
WITH TB AS
(
SELECT 20120101 AS [Time],200 AS [Value] UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
)


SELECT a.[Time],a.[Value],SUM(b.[Value]) AS [SUM] 
FROM TB a JOIN TB b ON a.[Time]>=b.[Time]
GROUP BY a.[Time],a.[Value]
ORDER BY a.[Time]

--Time	Value	SUM
--20120101	200	200
--20120104	100	700
--20120104	400	700
--20120401	200	900
--20120411	210	1110
--20120501	100	1210
灵雨飘零 2013-09-30
  • 打赏
  • 举报
回复
引用 5 楼 feisheng512 的回复:
lz 还是简单点好,想太复杂了你。。。

WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
)
SELECT tim,val,(SELECT SUM(ISNULL(val,0)) FROM a WHERE tim<=b.tim) AS lj FROM a b 
ORDER BY b.tim
/*
tim         val         lj
----------- ----------- -----------
20120101    200         200
20120104    100         700
20120104    400         700
20120401    200         900
20120411    210         1110
20120501    100         1210
20121121    212         1422

(7 行受影响)
*/
你的结果不对哦!是你想的太简单了。
北极海hein 2013-09-30
  • 打赏
  • 举报
回复



drop table #tb
select 时间,值,null as total into #tb from tablename order by 时间

declare @Total int
set @Total=0
update #tb set total=@Total,@Total=@Total+值

select 时间,值,total from #tb order by 时间
feisheng512 2013-09-30
  • 打赏
  • 举报
回复
改进了下,之前疏忽

WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
),b AS
(
	SELECT *,ROW_NUMBER() OVER( ORDER BY tim) AS rownum FROM a
)
SELECT tim,val,(SELECT SUM(ISNULL(val,0)) FROM b WHERE rownum<=c.rownum) AS lj FROM b c 
ORDER BY c.tim
/*
tim         val         lj
----------- ----------- -----------
20120101    200         200
20120104    100         300
20120104    400         700
20120401    200         900
20120411    210         1110
20120501    100         1210
20121121    212         1422

(7 行受影响)
*/
feisheng512 2013-09-29
  • 打赏
  • 举报
回复
lz 还是简单点好,想太复杂了你。。。

WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
)
SELECT tim,val,(SELECT SUM(ISNULL(val,0)) FROM a WHERE tim<=b.tim) AS lj FROM a b 
ORDER BY b.tim
/*
tim         val         lj
----------- ----------- -----------
20120101    200         200
20120104    100         700
20120104    400         700
20120401    200         900
20120411    210         1110
20120501    100         1210
20121121    212         1422

(7 行受影响)
*/
Andy__Huang 2013-09-29
  • 打赏
  • 举报
回复

create table #tb(时间 varchar(10),值 int)
insert into #tb
select '20120101',200
union all select '20121121',212
union all select '20120104',100
union all select '20120411',210
union all select '20120501',100
union all select '20120401',200
union all select '20120104',400

select *,累计值=(select sum(值) from (select *,rn=row_number() over(order by 时间) from #tb) b where b.rn<=a.rn)
from 
(
select *,rn=row_number() over(order by 时间) from #tb
)a
order by 时间

/*
时间   值  累计值
-----------------------
20120101	200	200
20120104	100	300
20120104	400	700
20120401	200	900
20120411	210	1110
20120501	100	1210
20121121	212	1422
*/
灵雨飘零 2013-09-29
  • 打赏
  • 举报
回复
参考答案:
SELECT T1.A_TIME,T1.A_VALUE,(SELECT SUM(T2.A_VALUE) FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T2 WHERE T2.ROW<=T1.ROW ) AS LJ
FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T1

tcmakebest 2013-09-29
  • 打赏
  • 举报
回复
问题的解决方案很多,这个功能用高级语言解决是最容易的。
灵雨飘零 2013-09-29
  • 打赏
  • 举报
回复
自己解决了,嘎嘎!!!!!!

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧