oracle之函式LAG,LEAD

liqilin0429發表於2011-08-29
Oracle函式
第一：查詢出所有的記錄
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章