SqlServer 统计用户量实例（按年，月，日分组）

知识在于积累。好记星不如烂笔头。代码如下：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUsersOfStatistical]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
drop procedure [dbo].[GetUsersOfStatistical] 
go
create proc GetUsersOfStatistical
(
@TimeType varchar(50),
@Wherestr Varchar(3000)
)
as 
begin
declare @sqlstr varchar(max)
if(@TimeType='Year')
begin
set @sqlstr='select datename(year,CreateTime) as creatime,sum(case Str3 when 1 then 1 when 3 then 1 when 5 then 1 else 0 end ) as ygb ,sum(case Str3 when 0 then 1 when 2 then 1 when 4 then 1 else 0 end ) as wgb ,sum(1) as allcount from egb_user_audit ' +@Wherestr +' group by datename(year,CreateTime)'
end
else if(@TimeType='Month')
begin
set @sqlstr='select datename(year,CreateTime)+datename(month,CreateTime) as creatime,sum(case Str3 when 1 then 1 when 3 then 1 when 5 then 1 else 0 end ) as ygb ,sum(case Str3 when 0 then 1 when 2 then 1 when 4 then 1 else 0 end ) as wgb ,sum(1) as allcount from egb_user_audit ' +@Wherestr +' group by datename(year,CreateTime)+datename(month,CreateTime)'
end
else 
begin 
set @sqlstr='select Convert(varchar(10),CreateTime,120)as creatime,sum(case Str3 when 1 then 1 when 3 then 1 when 5 then 1 else 0 end ) as ygb ,sum(case Str3 when 0 then 1 when 2 then 1 when 4 then 1 else 0 end ) as wgb ,sum(1) as allcount from egb_user_audit ' +@Wherestr +' group by Convert(varchar(10),CreateTime,120)'
end
exec(@sqlstr);
end

exec GetUsersOfStatistical '','where Str2 in (10001)'