1 use ResourceShare 2 --统计使用情况 3 alter PROCEDURE StaSheryUse 4 @start datetime, 5 @end datetime, 6 @orgId int 7 AS 8 BEGIN 9 10 11 create table #Month 12 ( 13 id int IDENTITY (1,1) primary key not null, 14 [year] int not null, 15 [month] int not null 16 ) 17 18 19 --计算全文传递数 20 21 select * into #FullTextTask from (select COUNT(1) fulltextCount,YEAR(CreateDate) [year],MONTH(CreateDate) [month] from FullTextTask where OrganId=@orgId and ([State]=4 or [State]=5) and (CreateDate BETWEEN @start AND @end) group by YEAR(CreateDate),MONTH(CreateDate)) a 22 23 --计算数据库访问次数 24 25 select * into #DBVisitTask from (select COUNT(1) dbvisitCount,YEAR(CreateDate) [year],MONTH(CreateDate) [month] from DBVisitTask where OrganId=@orgId and ([State]=1 or [State]=3) and (CreateDate BETWEEN @start AND @end) group by YEAR(CreateDate),MONTH(CreateDate)) b 26 27 --计算用户数 28 select * into #IdentityUser from (select COUNT(1) userCount,YEAR(CreateTime) [year],MONTH(CreateTime) [month] from IdentityUser where OrganId=@orgId and (CreateTime BETWEEN @start AND @end) group by YEAR(CreateTime),MONTH(CreateTime)) c 29 30 --计算用户登录次数 31 select * into #UserLoginRecord from (select COUNT(1) loginTimes,YEAR(LoginDate) [year],MONTH(LoginDate) [month] from UserLoginRecord r left join IdentityUser u on r.UserId=u.Id where OrganId=@orgId and (LoginDate BETWEEN @start AND @end) group by YEAR(LoginDate),MONTH(LoginDate)) d 32 33 34 35 36 insert into #Month([year],[month]) select [year],[month] from #FullTextTask 37 insert into #Month([year],[month]) select [year],[month] from #DBVisitTask 38 insert into #Month([year],[month]) select [year],[month] from #IdentityUser 39 insert into #Month([year],[month]) select [year],[month] from #UserLoginRecord 40 41 42 select * into #yearAndMonth from(select [year],[month] from #Month group by [year],[month]) a 43 44 45 46 47 select * from (select isnull(userCount, 0) UserCount,isnull(dbvisitCount, 0) DBvisitCount,isnull(fulltextCount, 0) FulltextCount,isnull(loginTimes, 0) LoginTimes,m.[year] [Year],m.[month] [Month] from #yearAndMonth m 48 49 left join #UserLoginRecord a on a.[year]=m.[year] and a.[month]=m.[month] 50 left join #DBVisitTask b on b.[year]=m.[year] and b.[month]=m.[month] 51 left join #FullTextTask c on c.[year]=m.[year] and c.[month]=m.[month] 52 left join #IdentityUser d on d.[year]=m.[year] and d.[month]=m.[month]) f order by [Year] desc,[Month] 53 54 END 55 56 57 execute StaSheryUse '2017-04-16','2017-04-17 23:59:59',7
这段代码，我简要地介绍下，存储过程接收了两个参数，一个开始日期，一个结束日期。根据传进来的日期范围统计全文传递数，用户注册数等。11行，创建了一个月份的临时表，包括年和月两个字段。它的数据来源于其它所有要统计的表。之后42行，定义了一个#yearAndMonth的临时表，主要是把#Month表中的重复数据过滤掉。最后把众多的统计临时表和#yearAndMonth左链接查询，即可得到结果，结果是根据年份和月份统计的二维表。