日期:2014-05-17 浏览次数:20514 次
--试下,应没错
select datepart(year,dateadd(day,-25,日期)),
sum(case when datepart(month,dateadd(day,-25,日期))=1 then 金额 else 0 end) [1月],
sum(case when datepart(month,dateadd(day,-25,日期))=2 then 金额 else 0 end) [2月],
sum(case when datepart(month,dateadd(day,-25,日期))=3 then 金额 else 0 end) [3月],
sum(case when datepart(month,dateadd(day,-25,日期))=4 then 金额 else 0 end) [4月],
sum(case when datepart(month,dateadd(day,-25,日期))=5 then 金额 else 0 end) [5月],
sum(case when datepart(month,dateadd(day,-25,日期))=6 then 金额 else 0 end) [6月],
sum(case when datepart(month,dateadd(day,-25,日期))=7 then 金额 else 0 end) [7月],
sum(case when datepart(month,dateadd(day,-25,日期))=8 then 金额 else 0 end) [8月],
sum(case when datepart(month,dateadd(day,-25,日期))=9 then 金额 else 0 end) [9月],
sum(case when datepart(month,dateadd(day,-25,日期))=10 then 金额 else 0 end) [10月],
sum(case when datepart(month,dateadd(day,-25,日期))=11 then 金额 else 0 end) [11月],
sum(case when datepart(month,dateadd(day,-25,日期))=12 then 金额 else 0 end) [12月]
from 你的表
where datepart(year,dateadd(day,-25,日期)) between 2008 and 2012
group by datepart(year,dateadd(day,-25,日期))
select year(日期) AS 年,
sum(case when MONTH(日期+7)=1 then 1 ELSE 0 END) as [1月],
sum(case when convert(VARCHAR(5),日期,10) between '01-25' and '02-26'then 金额 else 0 end) as [2月],
sum(case when convert(VARCHAR(5),日期,10) between '02-25' and '03-26'then 金额 else 0 end) as [3月],
sum(case when convert(VARCHAR(5),日期,10) between '03-25' and '04-26'then 金额 else 0 end) as [4月],
sum(case when convert(VARCHAR(5),日期,10) between '04-25' and '05-26'then 金额 else 0 end) as [5月],
sum(case when convert(VARCHAR(5),日期,10) between '05-25' and '06-26'then 金额 else 0 end) AS [6月],
sum(case when convert(VARCHAR(5),日期,10) between '06-25' and '07-26'then 金额 else 0 end) as [7月],
sum(case when convert(VARCHAR(5),日期,10) between '07-25' and '08-26'then 金额 else 0 end) as [8月],
sum(case when convert(VARCHAR(5),日期,10) between '08-25' and '09-26'then 金额 else 0 end) as [9月],
sum(case when convert(VARCHAR(5),日期,10) between '09-25' and '10-26'then 金额 else 0 end) as [10月],
sum(case when convert(VARCHAR(5),日期,10) between '10-25' and '11-26'then 金额 else 0 end) as [11月],
sum(case when convert(VARCHAR(5),日期,10) between '11-25' and '12-26'then 金额 else 0 end) as [12月]
FROM [tb]
GROUP BY year(日期)