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

Question

例如，我有2个时间表：T1

id time
1 18:12:02
2 18:46:57
3 17:49:44
4 12:19:24
5 11:00:01
6 17:12:45

和T2

id time
1 18:13:02
2 17:46:57

我需要从T1获取最接近T2的时间。这些表之间没有关系。应该是这样的：

select T1.calldatetime
from T1, T2 
where T1.calldatetime between 
T2.calldatetime-(
    select MIN(ABS(T2.calldatetime-T1.calldatetime))
    from T2, T1)
and
T2.calldatetime+(
    select MIN(ABS(T2.calldatetime-T1.calldatetime))
    from T2, T1)

但是我不明白。有什么建议？

Answer 1

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

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分析功能，性能会更好。