首先还是先来创建一个数据表:
create table InStore
(
Id int ,
ProName varchar(200),
InsertCount int ,
InsertDay datetime
)
insert into instore values(1,'N82',10,'2010-06-01')
insert into instore values(2,'N95',15,'2010-05-11')
insert into instore values(3,'N78',12,'2010-06-11')
insert into instore values(4,'N79',11,'2010-05-08')
insert into instore values(5,'N82',10,'2010-06-07')
insert into instore values(6,'N79',13,'2010-06-01')
insert into instore values(7,'N97',17,'2010-06-08')
insert into instore values(8,'N86',25,'2010-06-01')
insert into instore values(9,'N85',10,'2010-04-08')
insert into instore values(10,'N81',19,'2010-03-01')
insert into instore values(11,'N85',20,'2010-03-17')
insert into instore values(12,'N86',9,'2010-06-019')
insert into instore values(13,'N81',19,'2009-03-01')
insert into instore values(14,'N85',20,'2009-03-17')
insert into instore values(15,'N86',9,'2009-06-19')
/*
我们的目的是要对本数据库表的proname字段按月份统计
*/
/*接下来开始创建存储过程*/
create proc GetMonthSum
@Tb varchar(200), --来自哪一个表
@ColumnByGroup varchar(200),--要汇总统计的字段
@ColumnBySum varchar(200), --被统计的字段
@ColumnTime varchar(200), --要统计的时间(例如月份)
@year varchar(200) --外部传进来的参数(例如,代表的是查询年)
as
declare @sql varchar(max)
set @sql=''
declare @str varchar(max)
set @str=''
declare @i int
set @i=1
while @i<=12
begin
set @str=@str+
'(select isnull(sum('+@ColumnBySum+'),0) from instore where proname=a.proname '
+' and year('+@ColumnTime+')=' +@year
+' and month('+@ColumnTime+')='+cast(@i as varchar(30))+')'
+' as '+'['+@year+'年'+cast(@i as varchar(30))+'月份'+']'+','
set @i=@i+1
end
set @str=left(@str,len(@str)-1)
set @sql='select a.'+@ColumnByGroup+', '+@str+' from '+ @Tb+' a group by '+@ColumnByGroup
/*
exec GetMonthSum 'instore','proname','insertcount','insertday','2010'
select a.proname,
(select isnull(sum(insertcount),0) from instore where proname=a.proname and month(insertday)='4' ) as [2010年月份],
(select isnull(sum(insertcount),0) from instore where proname=a.proname and month(insertday)='5' ) as [2010年月份],
(select isnull(sum(insertcount),0) from instore where proname=a.proname and month(insertday)='6' ) as [2010年月份]
from instore a
group by proname
*/
exec(@sql)
/*执行该存储过程*/
exec GetMonthSum 'instore','proname','insertcount','insertday','2010'
备注:本存储过程也适用于别的数据库表的统计,只要传入对应的参数即可,是不是挺好用的啊,★★★