一个分组查询的月份报表的存储过程

根据用户在网站活动的记录,生成一个按机型分组的月份报表的存储过程.

这个存储过程通过左联接各个临时表,然后生成一个与要在网页上显示的报表格式的目标视图.

这样可以直接在asp.net中用gridview绑定这个存储过程,而不用写一个.cs文件的代码,便可以完成按机型分组的月份报表的任务.

存储过程代码如下:

CREATE PROC prc_MonthReporter_v_ClientAction
@iYear int,
@iMonth int
as
begin
 begin   transaction
 select identity(int,1,1) id
 ,vSupportPType
 ,nGameName
 ,count(id) iVisitTimeCount
 ,count(DISTINCT nMID) iVisitUserCount
 into #MonthVisitCount
 from  v_ClientAction
 where iActionTypeId=1
 and year(dActionTime)=@iYear and month(dActionTime)=@iMonth
 group by vSupportPType,nGameName
 order by vSupportPType
 
 select identity(int,1,1) id
 ,vSupportPType
 ,nGameName
 ,count(DISTINCT nMID) iOrderUserCount
 ,cast(count(id)*2 as int)  iIncomeing
 into #MonthOrderCount
 from  v_ClientAction
 where iActionTypeId=2
 and year(dActionTime)=@iYear and month(dActionTime)=@iMonth
 group by vSupportPType,nGameName
 order by vSupportPType
 
 select identity(int,1,1) id
 ,vSupportPType
 ,nGameName
 ,count(id) iDownloadTimeCount
 ,count(DISTINCT nMID) iDownloadUserCount
 into #MonthDownloadCount
 from  v_ClientAction
 where iActionTypeId=3
 and year(dActionTime)=@iYear and month(dActionTime)=@iMonth
 group by vSupportPType,nGameName
 order by vSupportPType
 
 select
 #MonthVisitCount.vSupportPType [终端型号]
 ,#MonthVisitCount.nGameName [游戏名称]
 ,isnull(#MonthVisitCount.iVisitTimeCount,0) [访问量]
 ,isnull(#MonthVisitCount.iVisitUserCount,0) [访问用户数]
 ,isnull(#MonthOrderCount.iOrderUserCount,0) [订购人数]
 ,isnull(#MonthDownloadCount.iDownloadTimeCount,0) [成功下载次数]
 ,isnull(#MonthDownloadCount.iDownloadUserCount,0) [成功下载人数]
 ,isnull(#MonthOrderCount.iIncomeing,0) [收入（元）]
 into #MonthReporter
 from #MonthVisitCount
 left outer join dbo.#MonthOrderCount
 on #MonthVisitCount.vSupportPType=#MonthOrderCount.vSupportPType
 and #MonthVisitCount.nGameName=#MonthOrderCount.nGameName
 left outer join dbo.#MonthDownloadCount
 on #MonthVisitCount.vSupportPType=#MonthDownloadCount.vSupportPType
 and  #MonthVisitCount.nGameName=#MonthDownloadCount.nGameName
 order by #MonthVisitCount.id
 
 --select * from #MonthVisitCount
 --select * from #MonthOrderCount
 --select * from #MonthDownloadCount
 
 select * from #MonthReporter
 
 if   object_id('tempdb.dbo.#MonthVisitCount') is not null
 drop   table #MonthVisitCount
 if   object_id('tempdb.dbo.#MonthOrderCount') is   not   null
 drop   table #MonthOrderCount
 if   object_id('tempdb.dbo.#MonthDownloadCount')is   not   null
 drop   table  #MonthDownloadCount
 if   object_id('tempdb.dbo.#MonthReporter')is   not   null
 drop   table #MonthReporter

 commit   transaction
end
GO
 

体会:

很多对数据库操作的编程,只要数据库中各表的关系设计得合理,都是可以能通过T-SQL来完成复杂的查询,插入与更新,联合等操作,然后达到接近业务需求逻辑的结果,再用编程与脚本语言来完成一些难以用T-SQL来完成的业务逻辑,是一个能让应用更高效的思路方法.