select r.[date],isnull(sum(t.VALUE),0) from ( select convert(varchar(5),dateadd(hour,number,cast('00:00' as datetime)),108)+'~'+ convert(varchar(5),dateadd(hour,number+1,cast('00:00' as datetime)),108) as [date] from master..spt_values where type = 'P' and number <= 23) r left join [tb] t on convert(varchar(5),DateAdd(s,t.[time],'1970-1-1 08:00:00'),108) between left(r.[date],5) and right(r.[date],5) group by r.[date]
不好意思,还有个问题。这个方法有个缺陷。就是如果统计数据里面没有这个时间段的数据,就会没有记录了。比如 00:00 ~ 01:00 之间没有数据那么报表就没有这个项目统计。能不能对于这个时间段没数据的情况一律显示为0呢? 比如,我刚的刚的数据都是 16:30 ~ 16:45之间的,但是我想其他时间段内的数据也显示成报表样式,只不过结果都是零而已。这样子我的报表就可以看到一天24小时内各个时间段的统计了。否则,就怕有的时间不连续,看起来就跳跃了。
--------------------------------------
00:00 ~ 00:15 0
00:15 ~ 00:30 0
...................
16:30 ~ 16:45 x
...................
16:45 ~ 17:00 0
...................
...................
23:45 ~ 24:00 0
--------------------------------------
俺就是不会啊!请各位达达给点指点。fredrichhu也说过,可是俺不知道如何使用。不哈意思。
convert(varchar(5),dateadd(hour,number+1,cast('00:00' as datetime)),108) as [date]
from master..spt_values
where type = 'P' and
number <= 23
---------------------
00:00~01:00
01:00~02:00
02:00~03:00
03:00~04:00
04:00~05:00
05:00~06:00
06:00~07:00
07:00~08:00
08:00~09:00
09:00~10:00
10:00~11:00
11:00~12:00
12:00~13:00
13:00~14:00
14:00~15:00
15:00~16:00
16:00~17:00
17:00~18:00
18:00~19:00
19:00~20:00
20:00~21:00
21:00~22:00
22:00~23:00
23:00~00:00
a.时间,isnull(b.数据,0) as 数据
from
构造时间的表 a
left join
数据表 b
on
关联条件
--这是什么时间格式?
TIME, ID1, ID2, ID3, TYPE, VALUE
----------------------------------------------------------
1253435278 37656 1 6911 2 100
1253435279 37651 1 9991 2 76
UTC时间,单位是秒。DateAdd(s,[time],'1970-1-1 08:00:00')这样子可以把它变为当前时区时间
select
r.[date],isnull(sum(t.VALUE),0)
from
(
select convert(varchar(5),dateadd(hour,number,cast('00:00' as datetime)),108)+'~'+
convert(varchar(5),dateadd(hour,number+1,cast('00:00' as datetime)),108) as [date]
from master..spt_values
where type = 'P' and
number <= 23) r
left join
[tb] t
on convert(varchar(5),DateAdd(s,t.[time],'1970-1-1 08:00:00'),108)
between left(r.[date],5) and right(r.[date],5)
group by r.[date]