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

Question

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        24sql查询语句能够查出本年本月日累计值，如果再加上同期的值呢？
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)
where year(a.dt)=2015 and month(a.dt)=1 group by a.dt

Answer 1

select a.dt,SUM(j1),SUM(j2),SUM(j3),SUM(j4),SUM(q1),SUM(q1),SUM(q1),SUM(q1) from (
select a.dt,SUM(b.rfdl1) rfdl1,SUM(b.rfdl2) rfdl2 ,SUM(b.rfdl3) rfdl3 ,SUM(b.rfdl4) rfdl4,0,0,0,0
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)
where year(a.dt)=2015 and month(a.dt)=1 group by a.dt
union all
select a.dt,0,0,0,0,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)
where year(a.dt)=2015 and month(a.dt)=1 group by a.dt )a(dt,j1,j2,j3,j4,q1,q2,q3,q4)
group by dt

Answer 2

with t (dt,rfdl1,rfdl2,rfdl3,rfdl4) as (
select '2015-01-01',1,11,111,111 union all
select '2015-01-02',2,22,222,2222 union all
select '2015-01-03',3,33,333,3333 union all
select '2014-01-01',4,44,444,4444 union all
select '2014-01-02',5,55,555,5555 union all
select '2014-01-03',6,66,666,6666
)
select 
'2015-'+SUBSTRING(CONVERT(varchar(10),a.dt,120),6,5)
,SUM(case YEAR(b.dt) when 2015 then b.rfdl1 else 0 end) 本年rfdl1
,SUM(case YEAR(b.dt) when 2015 then b.rfdl2 else 0 end) 本年rfdl2
,SUM(case YEAR(b.dt) when 2015 then b.rfdl3 else 0 end) 本年rfdl3
,SUM(case YEAR(b.dt) when 2015 then b.rfdl4 else 0 end) 本年rfdl4
,SUM(case YEAR(b.dt) when 2014 then b.rfdl1 else 0 end) 去年rfdl1
,SUM(case YEAR(b.dt) when 2014 then b.rfdl2 else 0 end) 去年rfdl2
,SUM(case YEAR(b.dt) when 2014 then b.rfdl3 else 0 end) 去年rfdl3
,SUM(case YEAR(b.dt) when 2014 then b.rfdl4 else 0 end) 去年rfdl4
from t a join t b on YEAR(a.dt)=2015 and month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt)
where YEAR(a.dt) between 2014 and 2015
group by '2015-'+SUBSTRING(CONVERT(varchar(10),a.dt,120),6,5)

Answer 3

where 条件可以不要了，因为on里面已经限定了：YEAR(a.dt)=2015

Answer 4

month(b.dt)=month(a.dt) and day(b.dt)<=day(a.dt)
改成
DATEPART(DAYOFYEAR,b.dt)<=DATEPART(DAYOFYEAR,a.dt)

Answer 5

WITH tb_tmp AS
(
SELECT a.sold_date AS DATE,SUM(b.rfdl1) AS rfdl1,SUM(b.rfdl2) AS rfdl2,SUM(b.rfdl3) AS rfdl3,SUM(b.rfdl4) AS rfdl4 FROM soldList a LEFT JOIN soldList b ON YEAR(a.sold_date)=YEAR(b.sold_date) AND MONTH(a.sold_date)=MONTH(b.sold_date) AND DAY(a.sold_date)>=DAY(b.sold_date)
GROUP BY a.sold_date
)SELECT a.DATE,
ISNULL(a.rfdl1,0) AS rfdl1This,
ISNULL(a.rfdl2,0) AS rfdl2This,
ISNULL(a.rfdl3,0) AS rfdl3This,
ISNULL(a.rfdl4,0) AS rfdl4This,
ISNULL(b.rfdl1,0) AS rfdl1Pre,
ISNULL(b.rfdl2,0) AS rfdl2Pre,
ISNULL(b.rfdl3,0) AS rfdl3Pre,
ISNULL(b.rfdl4,0) AS rfdl4Pre 
FROM tb_tmp a LEFT OUTER JOIN tb_tmp b ON YEAR(a.DATE)=YEAR(b.DATE)+1 AND MONTH(a.DATE)=MONTH(b.DATE) AND DAY(a.DATE)=DAY(b.DATE)

Answer 6

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年1月3日相关的信息                   本日           同期（日）     同比         本月         同期（月）      同比         本年   同期（年）       同比
rfdl1             4                 9                      -5             12             15                     -3              12          15                 -3
rfdl2             7                10                   -3               20             18                      2               20            18           2
rfdl3             .............................................................
rfdl4            .....................................................................

Answer 7

如果一下很难统计出rfdl1,rfdl2,rfdl3,rfdl4的信息。
那么能单独统计出rfdl1的相关信息吗？在程序里控制逐个去统计。