求一SQL,想了许久都没解决!
表字段及记录如下:
ID T0 ReadTime
000000000001479.12 2007-2-26 00:15:43
000000000001479.12 2007-2-26 1:15:43
000000000001479.12 2007-2-26 2:15:43
000000000001479.12 2007-2-26 3:15:43
000000000001479.12 2007-2-26 4:15:43
000000000001479.12 2007-2-26 5:15:43
000000000001479.12 2007-2-26 6:15:43
000000000001479.12 2007-2-26 7:15:43
000000000001479.12 2007-2-26 8:15:43
000000000001479.12 2007-2-26 9:15:43
000000000001479.12 2007-2-26 10:15:43
000000000001479.12 2007-2-26 11:15:43
000000000001479.12 2007-2-26 12:15:43
000000000001479.12 2007-2-26 13:15:43
000000000001479.12 2007-2-26 14:15:43
000000000001479.12 2007-2-26 15:15:43
000000000001479.12 2007-2-26 16:15:43
000000000001479.12 2007-2-26 17:15:43
000000000001479.12 2007-2-26 18:15:43
000000000001479.12 2007-2-26 19:15:43
000000000001479.12 2007-2-26 20:15:43
000000000001479.12 2007-2-26 21:15:43
000000000001479.12 2007-2-26 22:15:43
000000000001479.12 2007-2-26 23:15:43
现在要将每天的数据分成24点来显示,比如2007-2-26 的数据(见上图),共有24条记录,把这24条记录取出来在一行分24个来显示,对应时间下面是这个时间点的值,如下:
0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12
[解决办法]
select id,
max(case when right(convert(varchar(13),readtime,120),2) = '01 ' then T0 else 0 end) '0:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '02 ' then T0 else 0 end) '1:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '03 ' then T0 else 0 end) '2:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '04 ' then T0 else 0 end) '3:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '05 ' then T0 else 0 end) '4:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '06 ' then T0 else 0 end) '5:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '07 ' then T0 else 0 end) '6:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '08 ' then T0 else 0 end) '7:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '09 ' then T0 else 0 end) '8:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '01 ' then T0 else 0 end) '9:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '10 ' then T0 else 0 end) '10:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '11 ' then T0 else 0 end) '11:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '12 ' then T0 else 0 end) '12:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '13 ' then T0 else 0 end) '13:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '14 ' then T0 else 0 end) '14:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '15 ' then T0 else 0 end) '15:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '16 ' then T0 else 0 end) '16:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '17 ' then T0 else 0 end) '17:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '18 ' then T0 else 0 end) '18:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '19 ' then T0 else 0 end) '19:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '20 ' then T0 else 0 end) '20:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '21 ' then T0 else 0 end) '21:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '22 ' then T0 else 0 end) '22:00 ',
max(case when right(convert(varchar(13),readtime,120),2) = '23 ' then T0 else 0 end) '23:00 '
from tb
group by id
[解决办法]
try
Select
Max(Case When DatePart(hh, ReadTime) = 0 Then T0 Else 0.00 End) As '0:00 ',
Max(Case When DatePart(hh, ReadTime) = 1 Then T0 Else 0.00 End) As '1:00 ',
Max(Case When DatePart(hh, ReadTime) = 2 Then T0 Else 0.00 End) As '2:00 ',
Max(Case When DatePart(hh, ReadTime) = 3 Then T0 Else 0.00 End) As '3:00 ',
Max(Case When DatePart(hh, ReadTime) = 4 Then T0 Else 0.00 End) As '4:00 ',
...
Max(Case When DatePart(hh, ReadTime) = 23 Then T0 Else 0.00 End) As '23:00 '
From
表
Group By
ID,
Convert(Varchar(10), ReadTime, 120)
[解决办法]
--建立测试环境
create table #tb(ID varchar(20),[T0] numeric(9,2),ReadTime datetime)
insert #tb(ID,[T0],ReadTime)
select '000000000001 ', '479.12 ', '2007-2-26 00:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 1:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 2:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 3:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 4:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 5:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 6:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 7:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 8:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 9:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 10:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 11:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 12:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 13:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 14:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 15:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 16:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 17:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 18:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 19:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 20:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 21:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 22:15:43 ' union all
select '000000000001 ', '479.12 ', '2007-2-26 23:15:43 '
go
--执行测试语句
select
max(case when datepart(hh,ReadTime) = 0 then T0 else 0 end) as [0:00]
,max(case when datepart(hh,ReadTime) = 1 then T0 else 0 end) as [1:00]
,max(case when datepart(hh,ReadTime) = 2 then T0 else 0 end) as [2:00]
,max(case when datepart(hh,ReadTime) = 3 then T0 else 0 end) as [3:00]
,max(case when datepart(hh,ReadTime) = 4 then T0 else 0 end) as [4:00]
,max(case when datepart(hh,ReadTime) = 5 then T0 else 0 end) as [5:00]
,max(case when datepart(hh,ReadTime) = 6 then T0 else 0 end) as [6:00]
,max(case when datepart(hh,ReadTime) = 7 then T0 else 0 end) as [7:00]
,max(case when datepart(hh,ReadTime) = 8 then T0 else 0 end) as [8:00]
,max(case when datepart(hh,ReadTime) = 9 then T0 else 0 end) as [9:00]
,max(case when datepart(hh,ReadTime) = 10 then T0 else 0 end) as [10:00]
,max(case when datepart(hh,ReadTime) = 11 then T0 else 0 end) as [11:00]
,max(case when datepart(hh,ReadTime) = 12 then T0 else 0 end) as [12:00]
,max(case when datepart(hh,ReadTime) = 13 then T0 else 0 end) as [13:00]
,max(case when datepart(hh,ReadTime) = 14 then T0 else 0 end) as [14:00]
,max(case when datepart(hh,ReadTime) = 15 then T0 else 0 end) as [15:00]
,max(case when datepart(hh,ReadTime) = 16 then T0 else 0 end) as [16:00]
,max(case when datepart(hh,ReadTime) = 17 then T0 else 0 end) as [17:00]
,max(case when datepart(hh,ReadTime) = 18 then T0 else 0 end) as [18:00]
,max(case when datepart(hh,ReadTime) = 19 then T0 else 0 end) as [19:00]
,max(case when datepart(hh,ReadTime) = 20 then T0 else 0 end) as [20:00]
,max(case when datepart(hh,ReadTime) = 21 then T0 else 0 end) as [21:00]
,max(case when datepart(hh,ReadTime) = 22 then T0 else 0 end) as [22:00]
,max(case when datepart(hh,ReadTime) = 23 then T0 else 0 end) as [23:00]
from #tb t
group by convert(varchar(10),ReadTime,120)
go
--删除测试环境
drop table #tb
go
/*--测试结果
0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12
（所影响的行数为 1 行）
*/
