求sql 或存储过程，谢谢各位大侠
TheTime iTypescore
2013-12-12 16:15:00.00015.5
2013-12-12 16:23:00.00015.7
2013-12-12 17:24:00.00015.5
2013-12-12 17:06:00.00025.9
2013-12-12 17:07:00.00025.5
想要的统计结果
itype 1-2点 2-3点 。。 16-17点。。 17-18 23点到 0 点，0点到1点
------------------------------------------------------------------------
1 0 0 5.5+5.7+5.5 0 0
2 0 0 0 5.9+5.5 0
就是统计 itype 一天24个时间段的每个时间段score之和
[解决办法]
你那个5.5+5.7+5.5是要展示的还是告诉别人要这样计算的？
[解决办法]
把sum改成avg，你试试：
--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.5union all
select '2013-12-12 16:23:00.000',1,5.7union all
select '2013-12-12 17:24:00.000',1,5.5union all
select '2013-12-12 17:06:00.000',2,5.9union 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 +
' from tb
group by itype'
exec( @sql)
/*
itype1-2点2-3点3-4点4-5点5-6点6-7点7-8点8-9点9-10点10-11点11-12点12-13点13-14点14-15点15-16点16-17点17-18点18-19点19-20点20-21点21-22点22-23点23-0点0-1点
10.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000003.7333331.8333330.0000000.0000000.0000000.0000000.0000000.0000000.000000
20.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000000.0000005.7000000.0000000.0000000.0000000.0000000.0000000.0000000.000000
*/
