如何在SQL连接条件不匹配时按日期获取前一条记录

你的需求本质是为dates表的每一行日期，匹配actual表中不晚于该日期的最近一条记录，以下是几种可行的SQL实现方式：

通用方案（适配多数数据库） #

通过子查询找到每个date对应的最大匹配business_date，再关联actual表获取数据：

SELECT
    a.business_date,
    a.id,
    d.date
FROM dates d
JOIN actual a 
  ON a.business_date = (
      SELECT MAX(business_date)
      FROM actual
      WHERE business_date <= d.date
  );

高效优化方案（针对PostgreSQL/MySQL 8.0+/SQL Server） #

如果数据库支持横向连接（LATERAL/CROSS APPLY），可以用更高效的方式，利用索引快速定位最近记录：

PostgreSQL 写法 #

SELECT
    a.business_date,
    a.id,
    d.date
FROM dates d
LEFT JOIN LATERAL (
    SELECT business_date, id
    FROM actual
    WHERE business_date <= d.date
    ORDER BY business_date DESC
    LIMIT 1
) a ON true;

SQL Server 写法 #

SELECT
    a.business_date,
    a.id,
    d.date
FROM dates d
CROSS APPLY (
    SELECT TOP 1 business_date, id
    FROM actual
    WHERE business_date <= d.date
    ORDER BY business_date DESC
) a;

MySQL 8.0+ 写法 #

也可以结合窗口函数实现：

WITH ranked_actual AS (
    SELECT
        a.business_date,
        a.id,
        d.date,
        ROW_NUMBER() OVER (PARTITION BY d.date ORDER BY a.business_date DESC) AS rn
    FROM dates d
    LEFT JOIN actual a ON a.business_date <= d.date
)
SELECT business_date, id, date
FROM ranked_actual
WHERE rn = 1;

结果验证 #

以上任意一种写法执行后，都会得到你期望的结果：

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