SQL-已将数据分组时的运行总计 [英] SQL - running total when data already grouped
本文介绍了SQL-已将数据分组时的运行总计的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试对某些数据进行累加运算,并且已经看到了一种简单的方法.但是,我已经对一些数据进行了分组,这使我的代码丢了.我目前有日期和付款类型,以及与之相关的总数.
I am trying to do a running total for some data, and have seen the easy way to do it. However, I have already grouped some data and this is throwing off my code. I currently have dates and payment types, and the totals that it relates to.
我目前所拥有的是:
create table #testdata
(
mdate date,
pmttype varchar(64),
totalpmtamt int
)
insert into #testdata
select getdate()-7, 'DD', 10
union
select getdate() -7, 'SO', 12
union
select getdate()-6, 'DD', 3
union
select getdate()-5, 'DD', 13
union
select getdate()-5, 'SO', 23
union
select getdate()-5, 'PO', 8
我想拥有的是:
mdate | paymenttype | totalpmtamt | incrtotal
2016-08-29 | DD | 10 | 10
2016-08-29 | SO | 12 | 22
2016-08-30 | DD | 3 | 25
2016-08-31 | DD | 13 | 38
2016-08-31 | SO | 8 | 46
2016-08-31 | PO | 23 | 69
我尝试将在这里找到的其他代码改编为:
I've tried adapting other code I've found here into:
select t1.mdate,
t1.pmttype,
t1.totalpmtamt,
SUM(t2.totalpmtamt) as runningsum
from #testdata t1
join #testdata t2 on t1.mdate >= t2.mdate and t1.pmttype >= t2.pmttype
group by t1.mdate, t1.pmttype, t1.totalpmtamt
order by t1.mdate
但是我得到的只是
mdate | paymenttype | totalpmtamt | incrtotal
2016-08-29 | DD | 10 | 10
2016-08-29 | SO | 12 | 22
2016-08-30 | DD | 3 | 13
2016-08-31 | DD | 13 | 26
2016-08-31 | SO | 8 | 34
2016-08-31 | PO | 23 | 69
任何人都可以帮忙吗?
推荐答案
ANSI进行累计总和的标准方法是:
The ANSI standard way of doing a cumulative sum is:
select t.*, sum(totalpmtamt) over (order by mdate) as runningsum
from #testdata t
order by t.mdate;
并非所有数据库都支持此功能.
Not all databases support this functionality.
如果您的数据库不支持该功能,我将进行相关子查询:
If your database doesn't support that functionality, I would go for a correlated subquery:
select t.*,
(select sum(t2.totalpmtamt)
from #testdata t2
where t2.mdate <= t.mdate
) as runningsum
from #testdata
order by t.mdate;
这篇关于SQL-已将数据分组时的运行总计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文