34,571
社区成员
发帖
与我相关
我的任务
分享
--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 +
' from tb
group by itype'
exec( @sql)
/*
itype 1-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点
1 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 3.733333 1.833333 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
2 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 5.700000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
*/
--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 +
' from tb
group by itype'
exec( @sql)
/*
itype 1-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点
1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 11.2 5.5 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 11.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0
*/
--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 + ',SUM(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)
/*
itype 1-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点
1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 11.2 5.5 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 11.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0
*/
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 + ',SUM(case when substring(convert(varchar(20),TheTime,120),12,2)>='+CAST(number as varchar)+
' and substring(convert(varchar(20),TheTime,120),12,2)<'+cAST(number+1 as varchar)+
' then score else 0 end) as ['+CAST(number as varchar)+'-'+CAST(number+1 as 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)
/*
itype 1-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-24点 24-25点
1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 11.2 5.5 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 11.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0
*/