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'
请问有没有更简便单的方法，多谢。
------解决思路----------------------
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