如何在 Oracle sql 中获取最近的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13929053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the closest dates in Oracle sql
提问by fen1ksss
For example, I have 2 time tables: T1
例如，我有 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
and T2
和T2
id time
1 18:13:02
2 17:46:57
I need to get time from T1 that are the closest to time from T2. There is no relationship between this tables. It should be something like this:
我需要从 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)
But I can't get it. Any suggestions?
但我无法得到它。有什么建议？
采纳答案by mavroprovato
I believe this is the query you are looking for:
我相信这是您正在寻找的查询：
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;
Make sure that the time columns have the same date part, because the t2.time - t1.time part won't work otherwise.
确保时间列具有相同的日期部分，否则 t2.time - t1.time 部分将不起作用。
EDIT: Thanks for the accept, but Ben's answer below is better. It uses Oracle analytic functions and will perform much better.
编辑：感谢您的接受，但 Ben 在下面的回答更好。它使用 Oracle 分析功能，性能会更好。
回答by Ben
You only have to use a single Cartesian join to solve you problem unlike the other solutions, which use multiple. I assume time is stored as a VARCHAR2. If it is stored as a date then you can remove the TO_DATE functions. If it is stored as a date (I would highly recommend this), you will have to remove the date portions
与使用多个的其他解决方案不同，您只需使用一个笛卡尔连接即可解决您的问题。我假设时间存储为 VARCHAR2。如果它存储为日期，那么您可以删除 TO_DATE 函数。如果它被存储为日期（我强烈推荐这个），你将不得不删除日期部分
I've made it slightly verbose so it's obvious what's going on.
我把它写得有点冗长，所以很明显发生了什么。
select *
from ( select id, tm
, rank() over ( partition by t2id order by difference asc ) as rnk
from ( select t1.*, t2.id as t2id
, abs( to_date(t1.tm, 'hh24:mi:ss')
- to_date(t2.tm, 'hh24:mi:ss')) as difference
from t1
cross join t2
) a
)
where rnk = 1
Basically, this works out the absolute difference between every time in T1 and T2 then picks the smallest difference by T2 ID; returning the data from T1.
基本上，这计算出 T1 和 T2 中每次之间的绝对差异，然后选择 T2 的最小差异ID；从 T1 返回数据。
Here it is in SQL Fiddle format.
这里是SQL Fiddle 格式。
The less pretty (but shorter) format is:
不太漂亮（但更短）的格式是：
select *
from ( select t1.*
, rank() over ( partition by t2.id
order by abs(to_date(t1.tm, 'hh24:mi:ss')
- to_date(t2.tm, 'hh24:mi:ss'))
) as rnk
from t1
cross join t2
) a
where rnk = 1
回答by knagaev
Another one way of using analytic functions. May be strange :)
另一种使用解析函数的方法。可能很奇怪:)
select id, time,
case
when to_date(time, 'hh24:mi:ss') - to_date(lag_time, 'hh24:mi:ss') < to_date(lead_time, 'hh24:mi:ss') - to_date(time, 'hh24:mi:ss')
then lag_time
else lead_time
end closest_time
from (
select id, tbl,
LAG(time, 1, null) OVER (ORDER BY time) lag_time,
time,
LEAD(time, 1, null) OVER (ORDER BY time) lead_time
from
(
select id, time, 1 tbl from t1
union all
select id, time, 2 tbl from t2
)
)
where tbl = 2
To SQLFiddle... and beyond!
到SQLFiddle......以及更多！
回答by pbhd
This one here selects that row(s) from T1, which has/have the smallest distance to any in T2:
这里的这一行从 T1 中选择行，它与 T2 中的任何行的距离最小：
select T1.id, T1.calldatetime from T1, T2
where ABS(T2.calldatetime-T1.calldatetime)
=( select MIN(ABS(T2.calldatetime-T1.calldatetime))from T1, T2);
(tested it with mysql, hope you dont get an ORA from that)
（用 mysql 测试过，希望你不会从中得到 ORA）
Edit: according to the last comment, it should be like that:
编辑：根据最后一条评论，它应该是这样的：
drop table t1;
drop table t2;
create table t1(id int, t time);
create table t2(id int, t time);
insert into t1 values (1, '18:12:02');
insert into t1 values (2, '18:46:57');
insert into t1 values (3, '17:49:44');
insert into t1 values (4, '12:19:24');
insert into t1 values (5, '11:00:01');
insert into t1 values (6, '17:12:45');
insert into t2 values (1, '18:13:02');
insert into t2 values (2, '17:46:57');
select ot2.id, ot2.t, ot1.id, ot1.t from t2 ot2, t1 ot1
where ABS(ot2.t-ot1.t)=
(select min(abs(t2.t-t1.t)) from t1, t2 where t2.id=ot2.id)
Produces:
产生：
id t id t
1 18:13:02 1 18:12:02
2 17:46:57 3 17:49:44
回答by Ajith Sasidharan
Try this query its little lengthy, I will try to optimize it
试试这个查询有点冗长，我会尝试优化它
select * from t1
where id in (
select id1 from
(select id1,id2,
rank() over (partition by id2 order by diff) rnk
from
(select distinct t1.id id1,t2.id id2,
round(min(abs(to_date(t1.time,'HH24:MI:SS') - to_date(t2.time,'HH24:MI:SS'))),2) diff
from
t1,t2
group by t1.id,t2.id) )
where rnk = 1);
