--查询每小时custom1的最大值和相应的时间点(如果某个小时内custom1为null在前后两个整点赋值为0)
select end_time, custom1
from sim_event_patrol
where rowid in
(select max(rowid)
from sim_event_patrol a
where a.src_host_name = 'Mp-group4/0/8'
and a.dvc_address = '10.15.1.3'
and a.end_time between
to_date('2013-08-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2013-08-21 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by to_char(a.end_time, 'yyyyMMdd hh24'))
order by end_timeoracle 查询优化 select oracleselect
select end_time, custom1
from sim_event_patrol
where rowid in
(select max(rowid)
from sim_event_patrol a
where a.src_host_name = 'Mp-group4/0/8'
and a.dvc_address = '10.15.1.3'
and a.end_time between
to_date('2013-08-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2013-08-21 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by to_char(a.end_time, 'yyyyMMdd hh24'))
order by end_timeoracle 查询优化 select oracleselect
2.至于查不到数据填充空值的情况,你可以尝试lead 和lag分析函数