oracle之函数LAG,LEAD

原创 Linux操作系统作者：liqilin0429 时间：2011-08-29 17:43:03 0 删除编辑

第一：查询出所有的记录

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/，如需转载，请注明出处，否则将追究法律责任。

上一篇： oracle 之errors

下一篇： oracle之errors

请登录后发表评论登录

全部评论

liqilin0429

注册时间：2010-08-13

博文量
100
访问量

196763

ITPub博客

oracle之函数LAG,LEAD

最新文章