请教关于日累计查询的问题

[gooyan]

dt rfdl1 rfdl2 rfdl3 rfdl4
2015-01-01 5 8 10 4
2015-01-02 3 5 15 6
2015-01-03 4 7 5 10
...................
2015-01-31


查询结果为
2015-01-01 5 8 10 4
2015-01-02 8 13 25 10
2015-01-03 12 20 30 20

sql查询语句为：
select a.dt ,
(select sum(b.rfdl1) from table b where year(b.dt)=year(a.dt) and month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt) ) rfdllj1,
(select sum(b.rfdl2) from table b where year(b.dt)=year(a.dt) and month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt) ) rfdllj2,
(select sum(b.rfdl3) from table b where year(b.dt)=year(a.dt) and month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt) ) rfdllj3,
(select sum(b.rfdl4) from table b where year(b.dt)=year(a.dt) and month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt) ) rfdllj4
from table a where year(a.dt)='2015' and month(a.dt)='1'

请问有没有更简便单的方法，多谢。

[spiritofdragon]

with t (dt,rfdl1,rfdl2,rfdl3,rfdl4) as (
select '2015-01-01',5,8,10,4 union all
select '2015-01-02',3,5,15,6 union all
select '2015-01-03',4,7,5,10
)
select 
	a.dt
	,SUM(b.rfdl1) rfdl1
	,SUM(b.rfdl2) rfdl2
	,SUM(b.rfdl3) rfdl3
	,SUM(b.rfdl4) rfdl4
from t a join t b on year(b.dt)=year(a.dt) and month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt)
group by a.dt

[gooyan]

多谢，另外如果将日期倒排呢？现在是正排的。

引用 1 楼 spiritofdragon 的回复:

with t (dt,rfdl1,rfdl2,rfdl3,rfdl4) as (
select '2015-01-01',5,8,10,4 union all
select '2015-01-02',3,5,15,6 union all
select '2015-01-03',4,7,5,10
)
select 
	a.dt
	,SUM(b.rfdl1) rfdl1
	,SUM(b.rfdl2) rfdl2
	,SUM(b.rfdl3) rfdl3
	,SUM(b.rfdl4) rfdl4
from t a join t b on year(b.dt)=year(a.dt) and month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt)
group by a.dt

[gooyan]

如果再加上同期的日累计值呢？ dt rfdl1 rfdl2 rfdl3 rfdl4 2014-01-01 1 2 3 4 2014-01-02 5 6 7 8 2014-01-03 9 10 11 12 2015-01-01 5 8 10 4 2015-01-02 3 5 15 6 2015-01-03 4 7 5 10 查询结果为 2015-01-01 5 8 10 4 1 2 3 4 2015-01-02 8 13 25 10 6 8 10 12 2015-01-03 12 20 30 20 15 18 21 24

[spiritofdragon]

在另一贴里已经回答