如何在Oracle SQL中获取最接近的日期

我相信这是您要查找的查询：

CREATE TABLE t1(id INTEGER, time DATE);CREATE TABLE t2(id INTEGER, time DATE);INSERT INTO t1 VALUES (1, TO_DATE ('18:12:02', 'HH24:MI:SS'));INSERT INTO t1 VALUES (2, TO_DATE ('18:46:57', 'HH24:MI:SS'));INSERT INTO t1 VALUES (3, TO_DATE ('17:49:44', 'HH24:MI:SS'));INSERT INTO t1 VALUES (4, TO_DATE ('12:19:24', 'HH24:MI:SS'));INSERT INTO t1 VALUES (5, TO_DATE ('11:00:01', 'HH24:MI:SS'));INSERT INTO t1 VALUES (6, TO_DATE ('17:12:45', 'HH24:MI:SS'));INSERT INTO t2 VALUES (1, TO_DATE ('18:13:02', 'HH24:MI:SS'));INSERT INTO t2 VALUES (2, TO_DATE ('17:46:57', 'HH24:MI:SS'));SELECt t1.*, t2.*  FROM t1, t2,       (  SELECt t2.id, MIN (ABS (t2.time - t1.time)) diff FROM t1, t2        GROUP BY t2.id) b WHERe ABS (t2.time - t1.time) = b.diff;

确保时间列具有相同的日期部分，否则t2.time-t1.time部分将无法正常工作。

编辑 ：感谢您的接受，但本的下面的答案是更好。它使用Oracle分析功能，性能会更好。