求SQL语句按时间段分组
recorddate recordid
2007-04-28 20:09:081
2007-04-28 20:09:082
2007-04-28 20:09:183
2007-04-28 20:09:224
2007-05-10 11:33:505
2007-05-10 11:33:516
2007-05-10 11:34:117
2007-05-10 11:34:178
2007-04-28 20:09:089
数据如上表，条件是指定一个时间段，例如两小时，每两个小时的ID加和
我想获取count（recordid）
select count(recordid),recorddate from T_record group by ???
group by 后边怎么写啊？？请教了！！
[解决办法]
--生成测试数据
declare @T table(recorddate datetime,recordid int)
insert into @t select '2007-04-28 20:09:08 ',1
union all select '2007-04-28 20:09:08 ',2
union all select '2007-04-28 20:09:18 ',3
union all select '2007-04-28 20:09:22 ',4
union all select '2007-05-10 11:33:50 ',5
union all select '2007-05-10 11:33:51 ',6
union all select '2007-05-10 11:34:11 ',7
union all select '2007-05-10 11:34:17 ',8
union all select '2007-04-28 20:09:08 ',9
union all select '2007-04-29 10:09:08 ',10
--解决方法
select b.sj,isnull(sl,0) as sl from
(
select sl,dateadd(hour,a.sj*2, '2007-4-28 ') as sj from
(
select count(*) as sl,sj=datediff(hour, '2007-4-28 ',recorddate)/2 from @t
where recorddate> = '2007-4-28 ' and recorddate < '2007-4-30 '
group by datediff(hour, '2007-4-28 ',recorddate)/2
)a
)c
right join
(
select dateadd(hour,ints.i, '2007-4-28 ') as sj
from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6
) as ints
where ints.i%2=0 and dateadd(hour,ints.i, '2007-4-28 ') < '2007-4-30 '
)b
on c.sj=b.sj
order by 1
