oracle按时间段查询

with a as (

     select 

       to_date('20150423','yyyymmdd')+1-(3/24)*(level-1) dt 

     from 

     dual connect by level <= 8

)

select 

     nvl(count,0) count,a.dt 

from a 

left join (

     select 

          count(*) as count , a.dt 

     from 

          alarm_record b ,

          a

     where 

          b.alarm_time <= a.dt 

          and b.alarm_time >= a.dt-(3/24)

          group by dt   

) c 

on c.dt = a.dt

order by a.dt

效果如下：