Sql Server统计报表案例

场景：查询人员指定年月工作量信息

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  procedure [dbo].[GetWorkLoadMain]
@year int,
@month int,
@UserId varchar(50)
as
begin
    declare @day varchar(50)
    set @day=CAST(@year as varchar)+'-'+RIGHT(('00'+cast(@month as varchar)),2)+'-01'

declare @sql varchar(max)
set @sql=''

select @sql=@sql+',['+Rtrim(ProjectName)+'] int NULL DEFAULT 0  '  from 
( select distinct ProjectName from ProRecord  where TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month  ) t
set @sql=stuff( @sql,1,1,'') 

create table  #temp (日期 varchar(50))
    declare @count int
    set @count=(select COUNT(1) from ProRecord  where TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month )
        if @count>0
        begin
        exec (' alter table  #temp   add  '+@sql+'')
        end

insert into #temp(日期)
select convert(varchar(10),dateadd(dd,number,convert(varchar(8),@day,120)+'01'),120) as time  
from master..spt_values   
where type='P' and   
dateadd(dd,number,convert(varchar(8),@day,120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,@day),120)+'01')

declare @日期  varchar(50)    
declare @ProjectName  varchar(50)     
declare @num int  
declare @ru1 varchar(50) 
if(ISNULL(@UserId,'')<>'') 
     begin
        declare mycursor cursor for select CONVERT(varchar(10),InsertTime,23) as 日期,ProjectName, count(1) as num from ProRecord where UserId= @UserId and TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month group by CONVERT(varchar(10),InsertTime,23) ,ProjectName
     end
else
     begin
        declare mycursor cursor for select CONVERT(varchar(10),InsertTime,23) as 日期,ProjectName, count(1) as num from ProRecord where TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month group by CONVERT(varchar(10),InsertTime,23) ,ProjectName
     end
     
open mycursor      
fetch next from mycursor into @日期,  @ProjectName,@num
while (@@fetch_status=0)  
begin      
    set  @ru1 =Rtrim(@ProjectName) 
            exec ('update #temp set ['+@ru1+']='+@num+'  where 日期='+''''+@日期+''''+'  ' )
            fetch next from mycursor into  @日期,  @ProjectName, @num
end  
close mycursor  

DEALLOCATE mycursor   

end 

declare @sqlnew varchar(8000)
set @sqlnew=''
select @sqlnew=@sqlnew+',sum(cast('+name+' as int)) as '+name from tempdb.dbo.syscolumns where id=object_id('tempdb.dbo.#temp') and name not in('日期') order by colid     --写不要汇总的列名

exec ('select * from #temp  where 1=1  union all select ''合计'' as 日期'+@sqlnew+' from #temp  ')


--exec [GetWorkLoadMain] 2017,6,''
--exec [GetWorkLoadMain] 2017,6,'admin'

呈现效果：

 

分析：

1.行转列

declare @sql varchar(max)
set @sql=''

select @sql=@sql+',['+Rtrim(ProjectName)+'] int NULL DEFAULT 0 ' from 
( select distinct ProjectName from ProRecord where TypeCode=0 and year(InsertTime)=@year and month(InsertTime)=@month ) t
set @sql=stuff( @sql,1,1,'')

create table #temp (日期 varchar(50))
exec (' alter table #temp add '+@sql+'')

2.查询当前日期所在月份所有日期

select convert(varchar(10),dateadd(dd,number,convert(varchar(8),'2017-06-01',120)+'01'),120) as time 
from master..spt_values 
where type='P' and 
dateadd(dd,number,convert(varchar(8),'2017-06-01',120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,'2017-06-01'),120)+'01')

3.游标处理数据

declare @日期 varchar(50) 
declare @ProjectName varchar(50) 
declare @num int 
declare @ru1 varchar(50) 
declare mycursor cursor for select CONVERT(varchar(10),InsertTime,23) as 日期,ProjectName, count(1) as num from ProRecord where UserId= @UserId and TypeCode=0 and year(InsertTime)=@year and month(InsertTime)=@month group by CONVERT(varchar(10),InsertTime,23) ,ProjectName
open mycursor 
fetch next from mycursor into @日期, @ProjectName,@num
while (@@fetch_status=0) 
begin 
set @ru1 =Rtrim(@ProjectName) 
exec ('update #temp set ['+@ru1+']='+@num+' where 日期='+''''+@日期+''''+' ' )
fetch next from mycursor into @日期, @ProjectName, @num
end 
close mycursor

DEALLOCATE mycursor

end

4.对动态列临时表进行汇总查询

注意：对于临时表，查询需加前缀 tempdb

declare @sqlnew varchar(8000)
set @sqlnew=''
select @sqlnew=@sqlnew+',sum('+name+' )  as '+name from tempdb.dbo.syscolumns where id=object_id('tempdb.dbo.#temp') and name not in('日期') order by colid --写不要汇总的列名

exec ('select ''合计'' as 日期'+@sqlnew+' from #temp ')