步骤：
1.从表中查询数据，进行按月分组。
2.与临时表关联
Create table tb(日期 Varchar(10),时间 Varchar(10),价格 float)
insert tb values('2009-01-01','02:00:00','12.55')
insert tb values('2009-02-22','02:00:00','26.55')
insert tb values('2009-01-22','04:00:00','10.52')
insert tb values('2009-02-22','06:00:00','23.55')
insert tb values('2009-03-22','08:00:00','52.65')
insert tb values('2009-04-22','10:00:00','23.21')
insert tb values('2009-05-22','12:00:00','32.55')
insert tb values('2009-06-22','14:00:00','15.54')
insert tb values('2009-01-22','16:00:00','18.44')
insert tb values('2009-02-22','18:00:00','20.10')
insert tb values('2009-03-22','20:00:00','34.48')
insert tb values('2009-04-22','22:00:00','18.55')
insert tb values('2010-05-23','00:00:00','42.51')
insert tb values('2010-05-23','02:00:00','36.64')
insert tb values('2011-05-23','04:00:00','66.22')
insert tb values('2012-05-23','06:00:00','54.41')
select 日期,avg(价格) as 平均价格 from tb Group by 日期
GO
create proc wsp
@year int
as
declare @i int
set @i=1
--当前年1-12月表
declare @t table (dt varchar(10))
while @i<=12
begin
if @i<10
insert @t values(cast(@year as char(4))+'-0'+cast(@i as varchar))
else
insert @t values(cast(@year as char(4))+'-'+cast(@i as varchar))
set @i=@i+1
end
--和临时表关联
select b.dt,ISNULL(tt.价格,0) as价格 from
(
select CONVERT(VARCHAR(7),aa.日期,120) as 日期,SUM(aa.价格) as 价格
from tb as aa group by CONVERT(VARCHAR(7),aa.日期,120)
) as tt right join @t as b on tt.日期=b.dt
union all
select f1= '总计' , sum(价格) 价格 from tb where year(tb.日期)=@year
go
--执行
exec wsp 2009