【求助】SQL 累计求和问题

2013-09-29 10:36:38 645 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL中累计求和与滑动求和函数sum() over()的用法按照一定规则汇总c的值，具体规则为以a分组，每组内按照b进行排序，汇总第一行至当前行的c的加和值。sum()：对某个字段求和。over()：开窗，按照某种规则，将数据分组、分窗口计算。：对所有行进行求和。：按照order by对应字段的顺序，进行累计求和，即第一行到当前行，默认order by是升序排序（asc），也可以通过指定降序排序（desc）。阅读详情

想实现如下累计求和，求指点：

时间值
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语句如何写？？？

...全文

Mysql按月累计求和思路：其实实现累计的核心就是将相同的表在做一次关联，让里面的值作为比较参数。按月累加：需求：结果表使用distinct过滤重复字段后count 表名：case_result 字段：gmt_created 创建时间；row_id 行ID 以下为统计的各个月份的count数据： select DATE_FORMAT(gmt_create,'%Y%m') months, count(DISTINCT... 阅读详情

灵雨飘零

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

自己解决了，嘎嘎！！！！！！

【求助】SQL 累计求和问题

相关推荐