sql T_sql 通用的动态按月份统计输出

 

首先还是先来创建一个数据表：

 

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'

 

 

 

备注：本存储过程也适用于别的数据库表的统计，只要传入对应的参数即可，是不是挺好用的啊，★★★