第一:查询出所有的记录
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博客 ” ,链接:http://blog.itpub.net/20976446/viewspace-706160/,如需转载,请注明出处,否则将追究法律责任。