如何获得在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分析功能，并且性能会更好。

Answer 2

这里这一次选择从T1，它具有行（S）/在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); 

（与MySQL测试吧，希望你不要从得到一个ORA）

编辑：根据最后的评论，它应该是这样的：

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) 

产地：

id  t  id  t 
1  18:13:02  1  18:12:02 
2  17:46:57  3  17:49:44 

Answer 3

你只需要使用一个笛卡儿连接解决你不像其他的解决方案，它使用多个问题。我假设时间存储为VARCHAR2。如果它被存储为一个日期，那么你可以删除TO_DATE功能。如果（我会极力推荐这）存储为一个日期，你将需要删除的日期部分

我做了稍微详细所以很明显这是怎么回事。

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 

基本上，这工作进行每一次之间的绝对差在T1和T2然后拾取由T2 ID最小差值;从T1返回数据。

这是SQL Fiddle format。

的非常少（但更短）的格式是：

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 

Answer 4

尝试此查询它的小长篇，我会尽力去优化它

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); 

Answer 5

使用分析功能的另一种方式。 可能奇怪:)

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 

要SQLFiddle ......和超越！