日期:2014-05-17 浏览次数:20500 次
--drop table tb
create table tb(TheTime datetime, iType int, score numeric(10,1))
insert into tb
select '2013-12-12 16:15:00.000', 1 ,5.5 union all
select '2013-12-12 16:23:00.000', 1 ,5.7 union all
select '2013-12-12 17:24:00.000', 1 ,5.5 union all
select '2013-12-12 17:06:00.000', 2 ,5.9 union all
select '2013-12-12 17:07:00.000', 2 ,5.5
go
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',avg(case when substring(convert(varchar(20),TheTime,120),12,2)>='+CAST(number%24 as varchar)+
' and substring(convert(varchar(20),TheTime,120),12,2)<'+cAST((number+1)%24 as varchar)+
' then score else 0 end) as ['+CAST(number%24 as varchar)+'-'+CAST((number+1)%24as varchar)+'点]'
from master..spt_values
where type = 'P' and number >=1 and number <=24
set @sql = 'select itype'+@sql&