求条存储过程

table
id name score money volume date state
PK varchar(50) int decimal int datetime int


求表中每月的数据 和去年同期的数据 结果集 

结果集为
2011 
  1月 2月3月 4月5月 6月7月 8月9月 10月
2010
  1月 2月3月 4月5月 6月7月 8月9月 10月

SQL code

create proc pr_anme
as
begin
select convert(varchar(7),[date],120),
       sum(case when month([date])=1 then [money] then 0 end),
       sum(case when month([date])=2 then [money] then 0 end),
       sum(case when month([date])=3 then [money] then 0 end),
       sum(case when month([date])=4 then [money] then 0 end),
       sum(case when month([date])=5 then [money] then 0 end),
       sum(case when month([date])=6 then [money] then 0 end),
       sum(case when month([date])=7 then [money] then 0 end),
       sum(case when month([date])=8 then [money] then 0 end),
       sum(case when month([date])=9 then [money] then 0 end),
       sum(case when month([date])=10 then [money] then 0 end),
       sum(case when month([date])=11 then [money] then 0 end),
       sum(case when month([date])=12 then [money] then 0 end)
from [table] where year([date]) in (2011,2010)
group by convert(varchar(7),[date],120)
end

SQL code

create proc pr_anme(@year int)
as
begin
select convert(varchar(7),[date],120),
       sum(case when month([date])=1 then [money] then 0 end),
       sum(case when month([date])=2 then [money] then 0 end),
       sum(case when month([date])=3 then [money] then 0 end),
       sum(case when month([date])=4 then [money] then 0 end),
       sum(case when month([date])=5 then [money] then 0 end),
       sum(case when month([date])=6 then [money] then 0 end),
       sum(case when month([date])=7 then [money] then 0 end),
       sum(case when month([date])=8 then [money] then 0 end),
       sum(case when month([date])=9 then [money] then 0 end),
       sum(case when month([date])=10 then [money] then 0 end),
       sum(case when month([date])=11 then [money] then 0 end),
       sum(case when month([date])=12 then [money] then 0 end)
from [table] where year([date]) in (@year,@year-1)
group by convert(varchar(7),[date],120)
end

学习路过！！！