这条sql语句怎么写？想破了头

Question

1 116777484 2008-7-30 19:31:41
2 116777484 2008-7-30 19:31:42
3 116779574 2008-7-30 19:31:48
4 116779574 2008-7-30 19:31:50
5 116779574 2008-7-30 19:31:56
......如何用一个查询得到 
2-1   3-2   4-3  5-4 的结果2-1 是指 第二行的  时间 减去 第一行的时间不能用游标。

Answer 1

直接用 Lag(日期,1) 分析函数来查询

Answer 2

SQL> select * from test;COL1                                                                             COL2                                                                             COL3
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
1                                                                                116777484                                                                        2008-7-30 1
2                                                                                116779574                                                                        2008-7-30 1
3                                                                                116777484                                                                        2008-7-30 1
4                                                                                116779574                                                                        2008-7-30 1
5                                                                                116777484                                                                        2008-7-30 1SQL> select (a.col3-b.col3)*24*3600  as 秒 from test a,test b where a.col1=b.col1+1;        秒
----------
         1
         6
         2
         6

Answer 3

SQL> select idx,num,to_char(tdate,'yyyy-mm-dd hh24:mi:ss') from t;                                    IDX                                     NUM TO_CHAR(TDATE,'YYYY-MM-DDHH24:
--------------------------------------- --------------------------------------- ------------------------------
                                      1                               116777484 2008-07-30 19:31:41
                                      2                               116777484 2008-07-30 19:31:42
                                      3                               116779574 2008-07-30 19:31:48
                                      4                               116779574 2008-07-30 19:31:50
                                      5                               116779574 2008-07-30 19:31:56SQL> select idx,num,to_char(tdate,'yyyy-mm-dd hh24:mi:ss'),tdate-lag(tdate,1)over(order by tdate) difftime from t;                                    IDX                                     NUM TO_CHAR(TDATE,'YYYY-MM-DDHH24:   DIFFTIME
--------------------------------------- --------------------------------------- ------------------------------ ----------
                                      1                               116777484 2008-07-30 19:31:41            
                                      2                               116777484 2008-07-30 19:31:42            1.15740740
                                      3                               116779574 2008-07-30 19:31:48            6.94444444
                                      4                               116779574 2008-07-30 19:31:50            2.31481481
                                      5                               116779574 2008-07-30 19:31:56            6.94444444SQL> 

Answer 4

lead() over(partition by 1 order by id )