求SQL语句按时间段分组
recorddate recordid
2007-04-28 20:09:08 1
2007-04-28 20:09:08 2
2007-04-28 20:09:18 3
2007-04-28 20:09:22 4
2007-05-10 11:33:50 5
2007-05-10 11:33:51 6
2007-05-10 11:34:11 7
2007-05-10 11:34:17 8
2007-04-28 20:09:08 9
数据如上表,条件是指定一个时间段,例如两小时,每两个小时的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
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。