第一：查询出所有的记录

select *
from cdma_evt
where event like '%PPP%'
   and event <> 'PPP Hangup '
   and event <> 'PPP dial fail'
   and logid = 1346;

1 1346_603 1346 PPP Dial start  118.96137 26.32937 2010-12-14 12:52:59.357 51
2 1346_118 1346 PPP Dial start  118.95332 26.32552 2010-12-14 12:56:21.388 51
3 1346_119 1346 PPP success  118.95291 26.32531 2010-12-14 12:56:25.421 51
4 1346_199 1346 PPP Dial start  118.94567 26.32201 2010-12-14 13:01:46.902 51
5 1346_200 1346 PPP success  118.94567 26.32201 2010-12-14 13:01:50.935 51

第二：获取到上一条记录的时间

select c.id,c.logid,c.event,c.testtime,
       lag(c.testtime, 2, null) over(partition by c.logid order by c.testtime) t
from cdma_evt c
where c.event like '%PPP%'
   and c.event <> 'PPP Hangup '
   and c.event <> 'PPP dial fail'
and c.logid = 1346;

1 1346_603 1346 PPP Dial start 2010-12-14 12:52:59.357
2 1346_118 1346 PPP Dial start 2010-12-14 12:56:21.388
3 1346_119 1346 PPP success 2010-12-14 12:56:25.421 2010-12-14 12:52:59.357
4 1346_199 1346 PPP Dial start 2010-12-14 13:01:46.902 2010-12-14 12:56:21.388
5 1346_200 1346 PPP dial success 2010-12-14 13:01:50.935 2010-12-14 12:56:25.421

ITPub博客

oracle之函数LAG,LEAD

最新文章