如何在SQL中基于日期映射匹配历史数据的对应Stage值？

Table A #

Table B #

期望结果 #

需求说明 #

保留Table A的全部数据，新增来自Table B的Stage列。匹配规则为：根据Table A的Point_date，取Table B中对应ID、TENANT下该时间点的历史Stage值（即该Point_date之前或当天最近一次变更后的Stage）。

解决方案 #

方法1：使用LATERAL JOIN（适用于PostgreSQL、SQL Server、MySQL 8.0+） #

这种写法效率较高，针对Table A的每条记录，精准匹配符合条件的最新Stage：

SELECT 
    a.ID,
    a.Point_date,
    a.TENANT,
    b.STAGE
FROM 
    TableA a
LEFT JOIN LATERAL (
    SELECT STAGE
    FROM TableB b
    WHERE b.ID = a.ID 
      AND b.TENANT = a.TENANT 
      AND b.Change_date <= a.Point_date
    ORDER BY b.Change_date DESC
    LIMIT 1
) b ON TRUE
ORDER BY 
    a.ID, a.Point_date;

方法2：使用窗口函数（通用支持窗口函数的数据库） #

先对Table B的记录按ID、TENANT分组并按变更日期倒序排名，再关联Table A筛选出符合条件的最新记录：

WITH ranked_stages AS (
    SELECT 
        ID,
        TENANT,
        STAGE,
        Change_date,
        ROW_NUMBER() OVER (PARTITION BY ID, TENANT ORDER BY Change_date DESC) AS rn
    FROM TableB
)
SELECT 
    a.ID,
    a.Point_date,
    a.TENANT,
    rs.STAGE
FROM TableA a
LEFT JOIN ranked_stages rs 
    ON a.ID = rs.ID 
    AND a.TENANT = rs.TENANT 
    AND rs.Change_date <= a.Point_date
    AND rs.rn = 1
ORDER BY a.ID, a.Point_date;

注意事项 #

• 确保Point_date和Change_date字段为日期类型，如果是字符串格式，需要先转换为日期类型再比较，避免字符串排序导致的错误。例如：
  • MySQL：STR_TO_DATE(Point_date, '%m/%d/%y')
  • PostgreSQL：TO_DATE(Point_date, 'MM/DD/YY')
  • SQL Server：CAST(Point_date AS DATE)
• 如果存在多个相同ID、TENANT且Change_date相同的记录，ROW_NUMBER()会随机取一条，若想保留所有符合条件的记录，可替换为RANK()。

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