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

效果如下：