gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-04 00:30:46 26 4
gpt4 key购买 nike

例如,我有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)

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

最佳答案

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

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部分将无法正常工作。

编辑:感谢您的接受,但是Ben在下面的回答更好。它使用Oracle分析功能,性能会更好。

关于sql - 如何在Oracle SQL中获取最接近的日期,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13929053/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com