T-SQL 存储过程
prk/彭仁夔 08-09-02 转载注明出处。在开发中，我们经常要用到这样的统计
根据用户输入的日期，把每个月的备份表的数据和正在使用的表中的综合起来统计。假如2008-05-11到2008-08-01，那么就包含5，6，7，8四张备份表还有正在使用的表，加起5张表。这是要统计的所有的数据。
对于统计又有要求，分情况来统计各种情况的总计，但是有的特殊情况要统计其明细。
如下图：
T-sql日期转换不兼容。还有对运行的sql的长度也有一定的限制。
prk/彭仁夔 08-09-02 转载注明出处。
ALTER PROCEDURE .@StartTime varchar(100),@EndTime varchar(100),@OrgAddrsvarchar(1000)='',@MsgType_IDsvarchar(1000)='' AS declare @wherevarchar(1000) declare @startMonthint declare @endMonth int declare @tempMonth int declare @generalSql varchar(1000) declare @tempSql varchar(1000) declare @tempSql1 varchar(3000) declare @tempTotal varchar(3000) declare @tempUndeal varchar(3000) declare @tempdealing varchar(3000) declare @tempdealed varchar(3000) declare @temp1 varchar(3000) declare @temp2 varchar(3000) declare @temp3 varchar(3000) declare @temp4 varchar(3000) declare @mm int declare @mm1 int/* declare @StartTime varchar(100) declare @EndTime varchar(100) declare @OrgAddrsvarchar(1000) declare @MsgType_IDsvarchar(1000) set @StartTime ='2008-05-05' set@EndTime='2008-07-07' set@OrgAddrs ='' set@MsgType_IDs =''*/ set@startMonth=Month(@StartTime) set@endMonth =Month(@EndTime) set@tempMonth=@startMonth set@tempSql=' ' set@tempSql1=' ' set@temp1=' ' set@temp2=' ' set@temp3=' ' set@temp4=' ' set@where=' where recvTime>='''+CAST(@StartTime as varchar(20))+''' and recvTime<='''+CAST(@EndTime as varchar(20))+'''' if @OrgAddrs!='' begin set @where=@where+' '+ ' and OrgAddr in '+@OrgAddrs+' ' end if @MsgType_IDs!='' begin set @where=@where+' '+ ' and MsgType_ID in '+@MsgType_IDs+' ' end print @where set @generalSql=' select RecvSM_ID,User_ID,MsgType_ID,OrgAddr ,DestAddr, cast(RecvTime as DateTime) RecvTime, SM_Content,DealSign,cast(DealTime as DateTime) DealTime,IsWait,ReMsgType_ID,SendSM_Content from T_SMReceived '+@where+' and DealSign<>4and (DealSign<>8 and iswait<>0) and (DealSign<>3 and IsWait<>0)' print @generalSqlcreate table #temp(RecvSM_ID int, User_ID int, MsgType_ID int, OrgAddr varchar(128), DestAddr varchar(128), RecvTime datetime, SM_Content varchar(6000), DealSign tinyint, DealTime datetime, IsWait bit, ReMsgType_ID int , SendSM_Content varchar(6000) ) insert into #temp exec (@generalSql)while @tempMonth<=@endMonth begin set @tempSql='select RecvSM_ID,User_ID,MsgType_ID,OrgAddr ,DestAddr, cast(RecvTime as DateTime) RecvTime, SM_Content,DealSign,cast(DealTime as DateTime) DealTime,IsWait,ReMsgType_ID,SendSM_Content from T_SMReceived_'+Cast(@tempMonth as varchar(2))+''+@where+' ' insert into #tempexec (@tempSql) set @tempMonth= @tempMonth+1 end select @mm= count(*) from #temp print 'the temp table have:'+cast(@mm as varchar(100)) set @temp1='selectmsgtype_id,convert(varchar(10),recvtime,120) recvtime , total=count(*) from #temp' set @temp2=' group byconvert(varchar(10),recvtime,120), msgtype_id' set @tempTotal = @temp1+''+@temp2 print @tempTotal set @tempUndeal = @temp1+' where (iswait=1 and DealSign=0) or DealSign=2or (iswait=1 and DealSign=8) '+@temp2 print @tempUndeal set @tempdealing =@temp1+' where (iswait=1 and DealSign=1) or (iswait=1 and DealSign=9) '+@temp2 print @tempdealing set @tempdealed =@temp1+' where (iswait=0 and DealSign=3) or (iswait=0 and DealSign=4) '+@temp2 print @tempdealed set @temp3='select a.MsgType_ID , a.RecvTime,a.total, (select undeal= casewhen b.total IS NULLthen 0 else b.total end) undeal , (select dealing= casewhen c.total IS NULLthen 0 else c.total end) dealing, (select dealed= casewhen d.total IS NULLthen 0 else d.total end) dealed, undealDetail=null,dealingDetail=null,dealedDetail=null from ('+@tempTotal+') a left join (' +@tempUndeal+') b on a.msgtype_id=b.msgtype_id and a.recvtime=b.recvtime left join ('+ @tempdealing+' ) c on a.msgtype_id=c.msgtype_id and a.recvtime=c.recvtime left join ( ' +@tempdealed+') d on a.msgtype_id=d.msgtype_id and a.recvtime=d.recvtime' print '#temp1 SQL: '+ @temp3create table #temp1( MsgType_ID int, RecvTime varchar(10), total int, undeal int, dealing int, dealed int, undealDetail varchar(1000), dealingDetail varchar(1000), dealedDetail varchar(1000), )insert into #temp1 exec(@temp3) select @mm1= count(*) from #temp1print 'the #templ have :'+cast(@mm1 as varchar(100))declare finalReport cursor for select MsgType_ID,convert(varchar(10),recvtime,120) RecvTime ,dealed from #temp1 open finalReport declare @MsgType_IDf int declare @RecvTimef varchar(10) declare @dealedf int print'start tatal one record'; fetch next from finalReport into @MsgType_IDf, @RecvTimef ,@dealedf while(@@fetch_status=0) begin declare my_cursor cursor forselect a.msgtype_id ,a.recvtime,a.size,a.reMsgType_ID,b.ReMsgTypeName from (select msgtype_id,count(*) size, convert(varchar(10),recvtime,120) recvtime,reMsgType_ID from #temp where convert(varchar(10),recvtime,120)=@RecvTimef and msgtype_id=@MsgType_IDf and (iswait=0 and (DealSign=3 or DealSign=4)) group byconvert(varchar(10),recvtime,120), msgtype_id,reMsgType_ID) a left join T_ReMsgType b on a.reMsgType_ID=b.reMsgType_ID order by a.msgtype_id open my_cursor declare @msgtype_id int declare @recvtime varchar(10) declare @reMsgType_ID int declare @ReMsgTypeName varchar(500) declare @dealedDetail varchar(500) declare @size int declare @flag int set @flag=0 set @dealedDetail='总数:'+Cast(@dealedf as varchar(5))+' (其中 ' print'start deal one record'; fetch next from my_cursor into @msgtype_id,@recvtime,@size,@reMsgType_ID,@ReMsgTypeName while(@@fetch_status=0) begin set @dealedDetail= @dealedDetail+@ReMsgTypeName+': '+ Cast((select undeal= casewhen @size IS NULLthen 0else @size end) as varchar(5))+',' set @flag=1 fetch next from my_cursor into @msgtype_id,@recvtime,@size,@reMsgType_ID,@ReMsgTypeName end if @flag=0 begin set @dealedDetail=substring(@dealedDetail,0,len(@dealedDetail)-3) end else begin set @dealedDetail=substring(@dealedDetail,0,len(@dealedDetail))+')' end close my_cursor DEALLOCATE my_cursor update #temp1 set dealedDetail= @dealedDetailwhere convert(varchar(10),recvtime,120)=@RecvTimef and msgtype_id=@MsgType_IDf print'have update one record'; fetch next from finalReport into @MsgType_IDf, @RecvTimef ,@dealedf end close finalReport DEALLOCATE finalReport select c.*,d.MsgTypeName from #temp1 c left join T_MsgType d on c.msgtype_id=D.msgtype_idorder by c.RecvTime,c.msgtype_id
页:
[1]