sql按小时分组 返回 最近24小时 数据

假如我现在有 Table1

-------------------------------------Table1 (OptionID int OptionTime datetime)

OptionID    OptionTime

1           2008-07-26 10:28:28.000
2           2008-07-26 10:29:28.000

......

100         2008-07-27  8:28:28.000
101         2008-07-27  9:28:28.000
102         2008-07-27  9:30:28.000
103         2008-07-27  10:28:28.000
104         2008-07-27  11:28:28.000
105         2008-07-27  11:29:28.000
105         2008-07-27  12:30:28.000

......

200         2008-07-28  10:30:28.000
201         2008-07-28  10:31:28.000

....... 

***         当前时间 (假如现在是2008-07-27 10:40:30)

---------------------------------------------

我现在想得到：

以当前小时为终点，前推24小时，以小时为单位。

CountNum      TheTime
2             2008-07-27 11:00:00-11:59:59
1             2008-07-27 12:00:00-12:59:59

......
2             2008-07-28 10:00:00-10:59:59


----------------------------

如何？？

30 个解决方案

#1

按小时分组：
select dateadd(hour,datediff(hour,thetime,'19000101'),'19000101') from table

#2

时间在变化啊

#3

看不懂

#4

取最近的24小时数据后，按 (2008-07-27 11:00:00-11:59:59 )这种格式分组

#5

TheTime  为小时段

CountNum 为该小时段内的 Count()数

描述的不够明白？
 

#6

写个存储过程，定义一个@开始时间，一个@结束时间
然后set @结束时间=getdate()
set @开始时间=dateadd(hour,-24,@结束时间)

然后 

select count(*),datepart(hour,OptionTime) from #temp where OptionTime>=@开始时间 and OptionTime<=@结束时间 group by datepart(hour,OptionTime)

大概是这个意思吧
select count(*),datepart(hour,OptionTime)
的datepart(hour,OptionTime) 这里你自己拼一下字符串吧，拼成你要显示的样子就可以。

#7

我很想问下： 这个变化的字符串是怎么拼的...

#8

引用 7 楼 gongsun 的回复:

我很想问下：这个变化的字符串是怎么拼的...

select ltrim(datepart(year,OptionTime))+'-'+ltrim(datepart(month,OptionTime))+'-'+ltrim(datepart(day,OptionTime))+' '+ltrim(datepart(hour,OptionTime))+':00:00-'+ltrim(datepart(hour,OptionTime))+':59:59'
from #temp group by datepart(hour,OptionTime),datepart(day,OptionTime),datepart(month,OptionTime),datepart(year,OptionTime)

上面的代码是专门拼接那个日期的，合在6楼的SQL里面就可以了。这是我想的最笨的方法，可能还有其他方法可以直接得到形如 1900-01-01 这样的字符串，那样就更简单了。

#9

declare @h datetime
set @h='2008-07-27 10:40:30'

select count(*) CountNum,
convert(varchar(13),OptionTime,120)+':00:00-'+right(convert(varchar(13),OptionTime,120),2)+':59:59' TheTime from table1 
where datediff(hh,@h,OptionTime)<=24 and datediff(hh,@h,OptionTime)>0
group by convert(varchar(13),OptionTime,120)+':00:00-'+right(convert(varchar(13),OptionTime,120),2)+':59:59'
-----------------------
2 2008-07-27 11:00:00-11:59:59
1 2008-07-27 12:00:00-12:59:59
2 2008-07-28 10:00:00-10:59:59

#10

引用 9 楼 wgzaaa 的回复:

declare @h datetime 
set @h='2008-07-27 10:40:30' 

select count(*) CountNum, 
convert(varchar(13),OptionTime,120)+':00:00-'+right(convert(varchar(13),OptionTime,120),2)+':59:59' TheTime from table1 
where datediff(hh,@h,OptionTime) <=24 and datediff(hh,@h,OptionTime)>0 
group by convert(varchar(13),OptionTime,120)+':00:00-'+right(convert(varchar(13),OptionTime,120),2)+':59:59' 
---------…

学习了。

#11

declare @endtime datetime
declare @starttime datetime

set @endtime=getdate() 
set @starttime=dateadd(hour,-24,@endtime) 

select isnull(count(OptionTime),0) as CountNum,

ltrim(datepart(year,OptionTime))+'-'+ltrim(datepart(month,OptionTime))+'-'+
ltrim(datepart(day,OptionTime))+' '+ltrim(datepart(hour,OptionTime))+
':00:00-'+ltrim(datepart(hour,OptionTime))+':59:59' as TheTime
from getAdverStatistics where advId=22 and OptionTime>=@starttime and OptionTime<=@endtime
 group by datepart(hour,OptionTime),
datepart(day,OptionTime),datepart(month,OptionTime),datepart(year,OptionTime)

怎么就不对呢？？？？？？？？？？

#12

我晕的着....半天大家还没完全明白我的意思...

也是我没说的明白...

近24小时，每个小时段的数据...如果该小时段没有数据 用 0 表示...

这个样子：

2 2008-07-27 11:00:00-11:59:59 
1 2008-07-27 12:00:00-12:59:59 
0 2008-07-27 13:00:00-13:59:59
0 2008-07-27 14:00:00-14:59:59

.......

2 2008-07-28 10:00:00-10:59:59 

#13

CountNum      TheTime  
2             2008-07-27 11:00:00-11:59:59 
1             2008-07-27 12:00:00-12:59:59 
0             2008-07-27 13:00:00-13:59:59 
0             2008-07-27 14:00:00-14:59:59 

....... 

2             2008-07-28 10:00:00-10:59:59 


//------------------------------

0 表示没有记录，数据库中没有这个时间段，那么该时间段如何出现？

#14

哇
那你这样，用临时表来处理吧
把那些没有的时间段也列出来

#15

那一天是不是要从
2008-07-27 00:00:00-01:59:59 
.
.
2008-07-27 23:00:00-23:59:59
是这样？

#16

--按楼上说的用临时表或表变量将select top 24 langid from master..syslanguages order by langid这句换掉也可以－－只是得到0到23这24个数
declare @h datetime
set @h='2008-07-27 10:40:30'
datediff(hh,@h,OptionTime)>0
select B.CountNum,TheTime+':00:00-'+right(TheTime,2)+':59:59' TheTime from 
(select top 24 langid from master..syslanguages order by langid)A 
left join 
( select count(*) CountNum,convert(varchar(13),OptionTime,120) TheTime from table1 
  where datediff(hh,@h,OptionTime)<=24 and datediff(hh,@h,OptionTime)>0
  group by convert(varchar(13),OptionTime,120)
)B on convert(varchar(13),dateadd(hh,A.langid+1,@h),120)=TheTime

#17

引用 14 楼 lgxyz 的回复:

哇 
那你这样，用临时表来处理吧 
把那些没有的时间段也列出来

又回到这个问题上：

这个时间段在不停的变化啊...

怎么列呢？

#18

怎么就不对呢
--select中出现的必须是group by中出现过的，单个字段，在select中可以出现计算字段，其他情况好象都不行
要么在聚合函数中是可以有其他情况的。

#19

引用 15 楼 lgxyz 的回复:

那一天是不是要从 
2008-07-27 00:00:00-01:59:59 
. 
. 
2008-07-27 23:00:00-23:59:59 
是这样？

不是的，是最近的24小时...最近，如果现在是 13:00-14:00这个时间段，那么数据就是从

昨天的14:00-15:00 到今天的 13:00-14:00 如此最近的24小时。


汗...

#20

创建临时表 左连接...

select top 24 langid from master..syslanguages order by langid

这句话什么意思？

能完整点吗？

#21

declare @h datetime
set @h='2008-07-28 10:40:30'--改日期了

select B.CountNum,id+':00:00-'+right(id,2)+':59:59' TheTime from 
( select top 24 convert(varchar(13),dateadd(hh,-langid,@h),120) id from master..syslanguages order by langid)A 
left join 
( select count(*) CountNum,convert(varchar(13),OptionTime,120) TheTime from table1 
  where datediff(hh,OptionTime,@h)<24 and datediff(hh,OptionTime,@h)>=0
  group by convert(varchar(13),OptionTime,120)
)B on A.id=B.TheTime
--------------------
2 2008-07-28 10:00:00-10:59:59
NULL 2008-07-28 09:00:00-09:59:59
NULL 2008-07-28 08:00:00-08:59:59
NULL 2008-07-28 07:00:00-07:59:59
NULL 2008-07-28 06:00:00-06:59:59
NULL 2008-07-28 05:00:00-05:59:59
NULL 2008-07-28 04:00:00-04:59:59
NULL 2008-07-28 03:00:00-03:59:59
NULL 2008-07-28 02:00:00-02:59:59
NULL 2008-07-28 01:00:00-01:59:59
NULL 2008-07-28 00:00:00-00:59:59
NULL 2008-07-27 23:00:00-23:59:59
NULL 2008-07-27 22:00:00-22:59:59
NULL 2008-07-27 21:00:00-21:59:59
NULL 2008-07-27 20:00:00-20:59:59
NULL 2008-07-27 19:00:00-19:59:59
NULL 2008-07-27 18:00:00-18:59:59
NULL 2008-07-27 17:00:00-17:59:59
NULL 2008-07-27 16:00:00-16:59:59
NULL 2008-07-27 15:00:00-15:59:59
NULL 2008-07-27 14:00:00-14:59:59
NULL 2008-07-27 13:00:00-13:59:59
1 2008-07-27 12:00:00-12:59:59
2 2008-07-27 11:00:00-11:59:59

#22

select top 24 langid from master..syslanguages order by langid
我写的都是用数据测试过的，所以都是完整的。
这句话是从master库中提取syslanguages 表中的前24个langid值，即取得0－23　这24个整数值

#23

isnull(count(*),0) 为什么 null 不变成 0 呢？

#24

B.CountNum--用
isnull(B.CountNum,0) CountNum
换下就变成0了

#25

麻痹地，感觉自己相当弱智..

Wgzaaa,请允许我骂你一句：牛比人...


分都给你...

#26

别人也分点特别是lgxyz 

#27

declare @endtime datetime
declare @starttime datetime
declare @temptime datetime
declare @count int
declare @timestr varchar(29)

set @endtime=getdate() 
set @starttime=dateadd(hour,-24,@endtime) 
set @temptime=@starttime
set @count=0

create table #tmp (CountNum int,TheTime varchar(29))
while (@count<24)
begin
	select @timestr= convert(varchar(13),@temptime,120)+':00:00-'+right(convert(varchar(13),@temptime,120),2)+':59:59' 
	insert into #tmp values (0,@timestr)
	select @temptime=dateadd(hour,1,@temptime)
	select @count=@count+1
end
select * from #tmp
--drop table #tmp

然后自己update去

#28

http://topic.csdn.net/u/20080728/09/49a12115-4ab2-4759-a3c8-1c96ecda2ea1.html

呵呵，无所谓了，我还开了个100的帖子，估计没人回答了...

你们去顶下，我就平均给分。

#29

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB 
GO
CREATE TABLE TB(OptionID int,OptionTime  datetime)
INSERT INTO TB SELECT 1,'2008-07-27 20:28:28.000'
INSERT INTO TB SELECT 2,'2008-07-27 20:29:28.000'
UNION ALL SELECT  3,'2008-07-27 18:28:28.000'
UNION ALL SELECT  4,'2008-07-27 19:28:28.000'
UNION ALL SELECT  5,'2008-07-27 19:30:28.000'



DECLARE @DT DATETIME,@I INT,@A VARCHAR(24)
SELECT @I=1,@DT=CONVERT(VARCHAR(13),DATEADD(DAY,-1,GETDATE()),120)+':00:00' 

DECLARE @TB TABLE(ST DATETIME,ED DATETIME)

WHILE @I<=24
BEGIN
  INSERT INTO @TB SELECT @DT,DATEADD(SS,-1,DATEADD(HH,1,@DT) )
  SELECT @I=@I+1,@DT=DATEADD(HH,1,@DT) 
END

SELECT NUM=SUM(CASE WHEN ISNULL(OptionID,0)=0 THEN 0 ELSE 1 END ),TheTime=CONVERT(VARCHAR,A.ST,120)+'-'+CONVERT(VARCHAR(8),A.ED,108)  FROM @TB A
LEFT JOIN TB B
  ON B.OptionTime BETWEEN A.ST AND A.ED
GROUP BY A.ST,A.ED
/*

NUM         TheTime                                 
----------- --------------------------------------- 
0           2008-07-27 14:00:00-14:59:59
0           2008-07-27 15:00:00-15:59:59
0           2008-07-27 16:00:00-16:59:59
0           2008-07-27 17:00:00-17:59:59
1           2008-07-27 18:00:00-18:59:59
2           2008-07-27 19:00:00-19:59:59
2           2008-07-27 20:00:00-20:59:59
0           2008-07-27 21:00:00-21:59:59
0           2008-07-27 22:00:00-22:59:59
0           2008-07-27 23:00:00-23:59:59
0           2008-07-28 00:00:00-00:59:59
0           2008-07-28 01:00:00-01:59:59
0           2008-07-28 02:00:00-02:59:59
0           2008-07-28 03:00:00-03:59:59
0           2008-07-28 04:00:00-04:59:59
0           2008-07-28 05:00:00-05:59:59
0           2008-07-28 06:00:00-06:59:59
0           2008-07-28 07:00:00-07:59:59
0           2008-07-28 08:00:00-08:59:59
0           2008-07-28 09:00:00-09:59:59
0           2008-07-28 10:00:00-10:59:59
0           2008-07-28 11:00:00-11:59:59
0           2008-07-28 12:00:00-12:59:59
0           2008-07-28 13:00:00-13:59:59

（所影响的行数为 24 行）

*/

#30

已经结了
哈哈