sqlserver存储过程及临时表在统计中的应用

 

 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左链接查询，即可得到结果，结果是根据年份和月份统计的二维表。