PostgreSQL两表关联：按ID匹配查询表1早于表2的最近日期数据

思路正确性判断 #

你提出的实现思路完全正确，属于这类同ID最近日期匹配场景的标准实现逻辑。

具体实现方式 #

方案1：关联后窗口函数取TOP1（全数据库兼容） #

先按你说的规则关联两张表，再用窗口函数分组排序取排名第一的记录即可，通用SQL示例：

WITH merge_tbl AS (
  SELECT
    t1.id,
    t1.date t1_date,
    t2.date t2_date,
    -- 补充需要查询的其他字段
    ROW_NUMBER() OVER(PARTITION BY t1.id, t2.date ORDER BY t1.date DESC) AS rn
  FROM table1 t1
  INNER JOIN table2 t2
  ON t1.id = t2.id AND t1.date <= t2.date
)
SELECT * FROM merge_tbl WHERE rn = 1;

如果同ID同最大日期下的多条表1记录都需要保留，将ROW_NUMBER()替换为RANK()即可。

方案2：LATERAL JOIN简化实现（支持新版本SQL引擎） #

如果你使用的是PostgreSQL、MySQL 8.0.14+、Spark SQL、Hive 2.0+等支持横向关联的引擎，可以用更简洁的写法，性能也更优：

SELECT 
  t2.*,
  t1_match.*
FROM table2 t2
LEFT JOIN LATERAL (
  SELECT * FROM table1 t1
  WHERE t1.id = t2.id AND t1.date <= t2.date
  ORDER BY t1.date DESC
  LIMIT 1
) t1_match ON TRUE;

边界情况处理 #

如果存在表2某条记录的日期早于对应ID下表1的所有日期，上面的内连接写法会过滤掉该记录，需要保留的话把INNER JOIN改为LEFT JOIN，缺失的表1字段会自动补NULL。

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