求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 |
|
来自: icecity1306 > 《数据库资料》