如何基于最近的较早日期关联两张SQL数据表？

要实现这个需求，核心是为table1的每条日期记录，匹配table2中小于等于该日期且差值最小的记录，这里推荐使用窗口函数ROW_NUMBER()来实现，逻辑清晰且性能较好。

实现步骤 #

• 先将table1和table2进行关联，只保留table2中日期早于或等于table1日期的记录；
• 计算每条匹配记录的日期差值（table1.date_col - table2.date_col），用于判断接近程度；
• 以table1的日期为分组依据，按日期差值升序排序，用ROW_NUMBER()为每组记录编号；
• 筛选出编号为1的记录，即为每个table1日期对应的最近更早的table2记录。

完整SQL代码 #

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
),
ranked_matches AS (
  SELECT
    t1.date_col AS t1_date,
    t2.date_col AS t2_date,
    t2.data_col,
    ROW_NUMBER() OVER (PARTITION BY t1.date_col ORDER BY (t1.date_col - t2.date_col) ASC) AS rn
  FROM table1 t1
  LEFT JOIN table2 t2 ON t2.date_col <= t1.date_col
)
SELECT t1_date, t2_date, data_col
FROM ranked_matches
WHERE rn = 1;

结果说明 #

执行后会得到：

• table1的2015-03-24 11:30:00匹配table2的2015-03-21 11:30:00（最近的更早日期）
• table1的2016-08-03 07:15:00匹配table2的2016-08-02 07:15:00
• table1的2016-02-29 22:30:00匹配table2的2016-02-28 22:30:00

如果table2中没有对应table1日期的更早记录，LEFT JOIN会保留table1的记录，对应的table2字段为NULL；若需过滤掉此类记录，将LEFT JOIN改为INNER JOIN即可。

内容的提问来源于stack exchange，提问作者Tom Tom