- A+
所属分类：SQLServer
自己改进版-存储过程
- ALTER PROCEDURE [dbo].[PROC_ROUTINE_GAME_STATISTICS]
- @minDate NVARCHAR(50) --开始时间
- ,@maxDate NVARCHAR(50) --结束时间
- ,@timeType NVARCHAR(5) = null --上下线类型
- ,@ProjectId NVARCHAR(32) = NULL--项目ID
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @SQLSTR NVARCHAR(MAX),@SQLColumns NVARCHAR(Max),
- @HourO NVARCHAR(Max),@HourN NVARCHAR(Max)
- SET @SQLColumns=N'convert(char(10),dateadd(d,number,'''+@minDate+'''),120) as INSERTDATE,'
- --循环拼接字符串
- declare @i int
- set @i=0
- while @i<23
- BEGIN
- --判断i是否小于10
- if(@i<10)
- BEGIN--小于10在前面追加0
- SET @HourO=N'0'+convert(varchar,@i)+':00'
- if(@i<9)--@HourN需要加1所以判断是否小于9
- BEGIN--小于9在前面追加0
- SET @HourN=N'0'+convert(varchar,(@i+1))+':00'
- END
- else
- BEGIN--大于等于9直接使用
- SET @HourN=convert(varchar,(@i+1))+':00'
- END
- END
- else
- BEGIN--大于等于10直接使用
- SET @HourO=convert(varchar,@i)+':00'
- SET @HourN=convert(varchar,(@i+1))+':00'
- END
- --拼接字符串
- SET @SQLColumns+=N'sum(case when convert(char(8),INSERTDATE,108) between '''+@HourO+''' and '''+@HourN+''' then 1 else 0 end) as '''+@HourO+'~'+@HourN+''','
- SET @i=@i +1
- END
- --根据时间段统计数据
- SET @SQLSTR=N'select '+@SQLColumns+'
- count(*) as ''sum''
- from TIME_INFO a right join
- master..spt_values b on datediff(d,INSERTDATE,dateadd(d,number,'''+@minDate+''')) = 0
- where dateadd(d,number,'''+@minDate+''') <= '''+@maxDate+'''
- and b.type = ''p'' and b.number >= 0
- and TIME_TYPE='+@timeType+' and GAME_ID='''+@ProjectId+'''
- group by convert(char(10),dateadd(d,number,'''+@minDate+'''),120)'
- --print @SQLSTR
- EXEC (@SQLSTR)
- END
执行结果
备查直接贴代码--引用网上
- DECLARE @minDate datetime,@maxDate datetime;
- SELECT @minDate = '2009-11-1',@maxDate = '2009-12-01';
- select convert(char(10),dateadd(d,number,@minDate),120),
- sum(case when convert(char(8),时间,108) between '00:00' and '01:00' then 1 else 0 end) as '00:00~01:00',
- sum(case when convert(char(8),时间,108) between '01:00' and '02:00' then 1 else 0 end) as '01:00~02:00',
- sum(case when convert(char(8),时间,108) between '02:00' and '03:00' then 1 else 0 end) as '02:00~03:00',
- sum(case when convert(char(8),时间,108) between '03:00' and '04:00' then 1 else 0 end) as '03:00~04:00',
- sum(case when convert(char(8),时间,108) between '04:00' and '05:00' then 1 else 0 end) as '04:00~05:00',
- sum(case when convert(char(8),时间,108) between '05:00' and '06:00' then 1 else 0 end) as '05:00~06:00',
- sum(case when convert(char(8),时间,108) between '06:00' and '07:00' then 1 else 0 end) as '06:00~07:00',
- sum(case when convert(char(8),时间,108) between '07:00' and '08:00' then 1 else 0 end) as '07:00~08:00',
- sum(case when convert(char(8),时间,108) between '08:00' and '09:00' then 1 else 0 end) as '08:00~09:00',count(a.列名1) as 'sum'
- from #tb a right join
- master..spt_values b on datediff(d,时间,dateadd(d,number,@minDate)) = 0
- where dateadd(d,number,@minDate) <= @maxDate and b.type = 'p' and b.number >= 0
- group by convert(char(10),dateadd(d,number,@minDate),120)
