declare @s varchar(8000),@i int
set @s = ''
@i = 1
while @i <= 24
begin
set @s = @s + ',['+rtrim(@i)+']=sum(case datepart(hh,dateadd(ss,-1,添加时间)) when '+rtrim(@i-1)+' then 1 else 0 end)'
set @i = @i + 1
end
set @s = 'select convert(char(10),添加时间,120)'+@s+' from 表 group by convert(char(10),添加时间,120) order by convert(char(10),添加时间,120)'
exec(@s)
set @s = ''
@i = 1
while @i <= 24
begin
set @s = @s + ',['+rtrim(@i)+']=sum(case datepart(hh,dateadd(ss,-1,添加时间)) when '+rtrim(@i-1)+' then 1 else 0 end)'
set @i = @i + 1
end
set @s = 'select convert(char(10),添加时间,120)'+@s+' from 表 group by convert(char(10),添加时间,120) order by convert(char(10),添加时间,120)'
exec(@s)
1(时间00:00:00-00:59:59) 2(时间01:00:00-01:59:59) ……24(时间23:00:00-00:59:59)
因为
2005-08-10 00:00:00 是属于2005-08-10的,而不是2005-08-09的
如果按照我的划分:select convert(datetime,convert(char(8),添加时间,112)) as 日期 ,
sum(case when datepart(hour,添加时间)=0 then 1 else 0 end) as [1],
sum(case when datepart(hour,添加时间)=1 then 1 else 0 end) as [2],
...
sum(case when datepart(hour,添加时间)=23 then 1 else 0 end) as [24]
from 表
where 添加时间>='2005-8-9'
and 添加时间<'2005-8-11'
group by convert(datetime,convert(char(8),添加时间,112))
(
[添加时间] varchar(10),
[添加时间] datetime
)
insert tb
select 'a','2005-08-9 00:18:05' union
select 'b','2005-08-9 01:19:05' union
select 'a','2005-08-9 01:26:05' union
select 'b','2005-08-9 23:18:05' union
select 'c','2005-08-9 23:28:05' --测试
declare @startDate datetime,@endDate datetime
select @startDate='2005-8-9',@endDate='2005-8-10'
declare @tb table([date] varchar(10))
while (@startDate<=@endDate)
begin
insert @tb select convert(char(10),@startDate,120)
set @startDate=dateadd(day,1,@startDate)
end
--select * from @tb
select A.*,
[1]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=0 then 1 else 0 end),
[2]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=1 then 1 else 0 end),
[3]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=2 then 1 else 0 end),
[4]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=3 then 1 else 0 end),
[5]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=4 then 1 else 0 end),
[6]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=5 then 1 else 0 end),
[7]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=6 then 1 else 0 end),
[8]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=7 then 1 else 0 end),
[9]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=8 then 1 else 0 end),
[10]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=9 then 1 else 0 end),
[11]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=10 then 1 else 0 end),
[12]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=11 then 1 else 0 end),
[13]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=12 then 1 else 0 end),
[14]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=13 then 1 else 0 end),
[15]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=14 then 1 else 0 end),
[16]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=15 then 1 else 0 end),
[17]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=16 then 1 else 0 end),
[18]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=17 then 1 else 0 end),
[19]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=18 then 1 else 0 end),
[20]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=19 then 1 else 0 end),
[21]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=20 then 1 else 0 end),
[22]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=21 then 1 else 0 end),
[23]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=22 then 1 else 0 end),
[24]=sum(case when B.[添加时间] is null then 0 when datepart(hour,convert(datetime,B.[添加时间]))=23 then 1 else 0 end)from @tb A
left join tb B on datediff(day,A.[date],convert(datetime,B.[添加时间]))=0
group by A.[date]--删除测试环境
drop table tb--结果
/*
date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2005-08-09 1 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
2005-08-10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0(所影响的行数为 2 行)
*/