如何按较小的最近日期联接表

我们可以使用易于理解的方法，：ROW_NUMBERABS()

WITH table1 AS (
  SELECT TO_DATE('03/24/2015 11:30:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col FROM DUAL UNION ALL
  SELECT TO_DATE('08/03/2016 07:15:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col FROM DUAL UNION ALL
  SELECT TO_DATE('02/29/2016 22:30:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col FROM DUAL
),
table2 AS (
  SELECT TO_DATE('03/20/2015 11:30:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col, 'table2_row1' AS data_col FROM DUAL UNION ALL
  SELECT TO_DATE('03/21/2015 11:30:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col, 'table2_row1' AS data_col FROM DUAL UNION ALL
  SELECT TO_DATE('03/25/2015 11:30:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col, 'table2_row2' AS data_col FROM DUAL UNION ALL
  SELECT TO_DATE('08/02/2016 07:15:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col, 'table2_row3' AS data_col FROM DUAL UNION ALL
  SELECT TO_DATE('08/04/2016 07:15:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col, 'table2_row4' AS data_col FROM DUAL UNION ALL
  SELECT TO_DATE('02/28/2016 22:30:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col, 'table2_row5' AS data_col FROM DUAL UNION ALL
  SELECT TO_DATE('03/01/2016 22:30:00', 'mm/dd/yyyy hh24:mi:ss') AS date_col, 'table2_row6' AS data_col FROM DUAL
)

SELECT *
FROM (
  SELECT table1.date_col,
    table2.date_col AS nearest_smaller_date,
    table2.data_col,
    ROW_NUMBER() OVER (PARTITION BY table1.date_col ORDER BY ABS(table1.date_col - table2.date_col)) AS rn
  FROM table1
  JOIN table2 ON table1.date_col >= table2.date_col
)
WHERE rn = 1
;

输出：

在 Oracle 12 中，使用 join 和 ：LATERALFETCH FIRST ROW ONLY

WITH table1 (date_col) AS (
  SELECT DATE '2015-03-24' + INTERVAL '11:30:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT DATE '2016-08-03' + INTERVAL '07:15:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT DATE '2016-02-29' + INTERVAL '22:30:00' HOUR TO SECOND FROM DUAL
),
table2 (date_col, data_col) AS (
  SELECT DATE '2015-03-20' + INTERVAL '11:30:00' HOUR TO SECOND, 'table2_row1' FROM DUAL UNION ALL
  SELECT DATE '2015-03-21' + INTERVAL '11:30:00' HOUR TO SECOND, 'table2_row1' FROM DUAL UNION ALL
  SELECT DATE '2015-03-25' + INTERVAL '11:30:00' HOUR TO SECOND, 'table2_row2' FROM DUAL UNION ALL
  SELECT DATE '2016-08-02' + INTERVAL '07:15:00' HOUR TO SECOND, 'table2_row3' FROM DUAL UNION ALL
  SELECT DATE '2016-08-04' + INTERVAL '07:15:00' HOUR TO SECOND, 'table2_row4' FROM DUAL UNION ALL
  SELECT DATE '2016-02-28' + INTERVAL '22:30:00' HOUR TO SECOND, 'table2_row5' FROM DUAL UNION ALL
  SELECT DATE '2016-03-01' + INTERVAL '22:30:00' HOUR TO SECOND, 'table2_row6' FROM DUAL
)
SELECT t1.date_col,
       t2.date_col AS nearest_earlier_date_col,
       t2.data_col
FROM   table1 t1
       LEFT OUTER JOIN LATERAL (
         SELECT *
         FROM   table2 t2
         WHERE  t2.date_col < t1.date_col
         ORDER BY date_col DESC
         FETCH FIRST ROW ONLY
       ) t2
       ON (1 = 1)

输出：

如果您想要最接近的日期（更早或更晚），那么：

WITH table1 (date_col) AS (
  SELECT DATE '2015-03-24' + INTERVAL '11:30:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT DATE '2016-08-03' + INTERVAL '07:15:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT DATE '2016-02-29' + INTERVAL '22:30:00' HOUR TO SECOND FROM DUAL
),
table2 (date_col, data_col) AS (
  SELECT DATE '2015-03-20' + INTERVAL '11:30:00' HOUR TO SECOND, 'table2_row1' FROM DUAL UNION ALL
  SELECT DATE '2015-03-21' + INTERVAL '11:30:00' HOUR TO SECOND, 'table2_row1' FROM DUAL UNION ALL
  SELECT DATE '2015-03-25' + INTERVAL '11:30:00' HOUR TO SECOND, 'table2_row2' FROM DUAL UNION ALL
  SELECT DATE '2016-08-02' + INTERVAL '07:15:00' HOUR TO SECOND, 'table2_row3' FROM DUAL UNION ALL
  SELECT DATE '2016-08-04' + INTERVAL '07:15:00' HOUR TO SECOND, 'table2_row4' FROM DUAL UNION ALL
  SELECT DATE '2016-02-28' + INTERVAL '22:30:00' HOUR TO SECOND, 'table2_row5' FROM DUAL UNION ALL
  SELECT DATE '2016-03-01' + INTERVAL '22:30:00' HOUR TO SECOND, 'table2_row6' FROM DUAL
)
SELECT t1.date_col,
       t2.date_col AS nearest_date_col,
       t2.data_col
FROM   table1 t1
       LEFT OUTER JOIN LATERAL (
         SELECT *
         FROM   table2 t2
         ORDER BY ABS(t1.date_col - t2.date_col) ASC
         FETCH FIRST ROW ONLY
       ) t2
       ON (1 = 1)

输出：

小提琴