日期:2014-05-17  浏览次数:20927 次

如何取指定日期的最近一笔数据?
环境 oracle

表 info 中有数据如下:

ID name login_time 

10 jack 2010/3/23 15:03:58.398000  

11 mike 2010/3/23 15:09:24.398000

12 john 2010/3/23 16:13:36.397000

13 jarry 2010/3/23 16:25:56.318000

14 jacky 2010/3/23 16:36:46.318000

想得到离日期2010/3/23 16:13:36.397000 最近的一笔数据,且此日期大于2010/3/23 16:13:36.397000,
即是得到如下记录:

13 jarry 2010/3/23 16:25:56.318000

请教各位,命令该如何写?


------解决方案--------------------
楼上写的都有问题啊
SQL code
--写一个timestamp的
SELECT *
  FROM temp
 WHERE id =
       (SELECT ok_id
          FROM (SELECT t.*,
                       lag(id) over(ORDER BY login_time) prev_id,
                       lead(id) over(ORDER BY login_time) next_id,
                       CASE
                         WHEN (scn_time - lag(scn_time) over(ORDER BY login_time)) -
                              (lead(scn_time) over(ORDER BY login_time) - scn_time) < 0 THEN
                          lag(id) over(ORDER BY login_time)
                         ELSE
                          lead(id) over(ORDER BY login_time)
                       END ok_id
                  FROM (SELECT id, NAME, login_time, timestamp_to_scn(login_time) scn_time FROM temp) t)
         WHERE login_time = to_timestamp('2010/3/23 16:13:36.397000', 'yyyy/mm/dd hh24:mi:ss.ff'));