场景：查询人员指定年月工作量信息
USE [Test]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[GetWorkLoadMain]@year int,@month int,@UserId varchar(50)asbegindeclare @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 ) tset @sql=stuff( @sql,1,1,'') create table #temp (日期 varchar(50))declare @count intset @count=(select COUNT(1) from ProRecord where TypeCode=0 and year(InsertTime)=@year and month(InsertTime)=@month )if @count>0beginexec (' alter table #temp add '+@sql+'')endinsert 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,'')<>'') begindeclare 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) ,ProjectNameendelsebegindeclare 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) ,ProjectNameendopen mycursorfetch next from mycursor into @日期, @ProjectName,@numwhile (@@fetch_status=0) beginset @ru1 =Rtrim(@ProjectName) exec ('update #temp set ['+@ru1+']='+@num+' where 日期='+''''+@日期+''''+' ' )fetch next from mycursor into @日期, @ProjectName, @numend 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] ,6,''--exec [GetWorkLoadMain] ,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 ) tset @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),'-06-01',120)+'01'),120) as time from master..spt_values where type='P' and dateadd(dd,number,convert(varchar(8),'-06-01',120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,'-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) ,ProjectNameopen mycursor fetch next from mycursor into @日期, @ProjectName,@numwhile (@@fetch_status=0) begin set @ru1 =Rtrim(@ProjectName) exec ('update #temp set ['+@ru1+']='+@num+' where 日期='+''''+@日期+''''+' ' )fetch next from mycursor into @日期, @ProjectName, @numend close mycursorDEALLOCATE mycursorend
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 ')
