SQL 月报表

步骤：

　　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

转载于:https://www.cnblogs.com/chencidi/archive/2010/03/29/1699530.html