如何同时统计本日，本月，本年以及同期，与同期比数据

[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
2014-01-04 13 14 15 16
2015-01-01 5 8 10 4
2015-01-02 3 5 15 6
2015-01-03 4 7 5 10
2015-01-04 5 6 7 8

怎么统计各类数据的本日、本月（本月1日截至本日）、本年（本年1月1日截至本日）的相应数据，
以及同期数据，与同比比较的数据。例如下面是统计2015年1月3日相关的信息（不包括3号以后得数据）

本日 同期（日） 同比 本月 同期（月） 同比 本年 同期（年） 同比
rfdl1 4 9 -5 12 15 -3 12 15 -3
rfdl2 7 10 -3 20 18 2 20 18 2
rfdl3 .............................................................
rfdl4 .....................................................................

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


下面的语句也能逐步实现，有没有更好的方法呢？

select rfdl.rfdl,rfdlly.rfdlly,(rfdl.rfdl-rfdlly.rfdlly) rfdlsub,yfdl.yfdl,yfdlly.yfdlly,(yfdl.yfdl-yfdlly.yfdlly) yfdlsub,nfdl.nfdl,nfdlly.nfdlly,(nfdl.nfdl-nfdlly.nfdlly) nfdlsub
from
(select rfdl1 rfdl from fdlmgr where datatime='2015-01-03') rfdl,
(select rfdl1 rfdlly from fdlmgr where datatime='2014-01-03') rfdlly,
(select sum(rfdl1) yfdl from fdlmgr where datatime>='2015-01-01' and datatime<='2015-01-03') yfdl,
(select sum(rfdl1) yfdlly from fdlmgr where datatime>='2014-01-01' and datatime<='2014-01-03') yfdlly,
(select sum(rfdl1) nfdl from fdlmgr where datatime>='2015-01-01' and datatime<='2015-01-03') nfdl,
(select sum(rfdl1) nfdlly from fdlmgr where datatime>='2014-01-01' and datatime<='2014-01-03') nfdlly

[gooyan]

求助啊，来人啊，围观啊

[firer2006]

这表谁设计的，说出来我保证不笑话它。

[道玄希言]

;with tbl(dt, rfdl1, rfdl2, rfdl3, rfdl4) as
(
select '2014-10-10', 1, 2, 3, 4 union all
select '2014-10-11', 5, 6, 7 , 8 union all
select '2014-10-12', 9, 10, 11, 12 union all
select '2014-10-13', 13, 14, 15, 16 union all
select '2015-10-10', 5, 8, 10, 4 union all
select '2015-10-11', 3, 5, 15, 6 union all
select '2015-10-12', 4, 7, 5, 10 union all
select '2015-10-13', 5, 6, 7, 8
),
tbl2 as
(
select dt, [品名], [数量] from tbl
unpivot
(
[数量] for [品名] in([rfdl1],[rfdl2],[rfdl3],[rfdl4])
) as T
)

select
t1.[品名],
t1.[数量] as [本日], t2.[数量] as [日同期],
(t1.[数量] - t2.[数量]) as [日同比],
t3.[数量] as [本月], t4.[数量] as [月同期],
(t3.[数量] - t4.[数量]) as [月同比],
t5.[数量] as [本年], t6.[数量] as [年同期],
(t5.[数量] - t6.[数量]) as [年同比]
from
(
select [品名],[数量] from tbl2 where dt = convert(nvarchar(10), getdate(), 120)
) as t1 -- 本日
left join
(
select [品名],[数量] from tbl2 where dt = convert(nvarchar(10), dateadd(YEAR, -1, getdate()), 120)
) as t2 -- 日同期
on t1.[品名] = t2.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(7), getdate(), 120) + '-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, getdate()), 120)
group by [品名]
) as t3 -- 本月
on t1.[品名] = t3.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(7), dateadd(YEAR, -1, getdate()), 120) + '-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, dateadd(YEAR, -1, getdate())), 120)
group by [品名]
) as t4 -- 月同期
on t1.[品名] = t4.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(4), getdate(), 120) + '-01-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, getdate()), 120)
group by [品名]
) as t5 -- 本年
on t1.[品名] = t5.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(4), dateadd(YEAR, -1, getdate()), 120) + '-01-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, dateadd(YEAR, -1, getdate())), 120)
group by [品名]
) as t6 -- 年同期
on t1.[品名] = t6.[品名]

------------------------------------------------------------------------------------------------------------------
品名 本日 日同期 日同比 本月 月同期 月同比 本年 年同期 年同比
rfdl1 4 9 -5 12 15 -3 12 15 -3
rfdl2 7 10 -3 20 18 2 20 18 2
rfdl3 5 11 -6 30 21 9 30 21 9
rfdl4 10 12 -2 20 24 -4 20 24 -4