如何基于跨表日期字段比对获取里程碑对应状态字段

核心匹配逻辑：同一Location维度下，任意里程碑日期对应的有效状态，是早于等于该里程碑日期的最新一条状态记录，无需按年月做模糊关联，直接做日期区间匹配即可。

方案1：窗口函数写法（兼容MySQL8+、PostgreSQL、SQL Server、Hive等支持窗口函数的引擎） #

先通过窗口函数给每条状态记录补全生效时间区间，再分别关联两个里程碑节点：

WITH status_with_interval AS (
    SELECT
        Location,
        Status_Date AS effective_start,
        -- 取同Location下一条状态的生效日期作为当前状态的失效点，无后续状态则设为永久未来日期
        LEAD(Status_Date, 1, '9999-12-31') OVER (
            PARTITION BY Location
            ORDER BY Status_Date
        ) AS effective_end,
        Status
    FROM Locations
)
SELECT
    c.Location,
    reg.Status AS Status_Registration,
    comp.Status AS Status_Completion
FROM Customers c
-- 关联注册节点对应状态
LEFT JOIN status_with_interval reg
    ON c.Location = reg.Location
    AND c.Registration >= reg.effective_start
    AND c.Registration < reg.effective_end
-- 关联完成节点对应状态
LEFT JOIN status_with_interval comp
    ON c.Location = comp.Location
    AND c.Completion >= comp.effective_start
    AND c.Completion < comp.effective_end

方案2：子查询写法（兼容所有SQL引擎，包括MySQL5.x等不支持窗口函数的版本） #

直接通过关联子查询，为每个里程碑日期匹配符合要求的最新状态：

SELECT
    c.Location,
    (
        SELECT Status
        FROM Locations l
        WHERE l.Location = c.Location
        AND l.Status_Date <= c.Registration
        ORDER BY l.Status_Date DESC
        LIMIT 1
    ) AS Status_Registration,
    (
        SELECT Status
        FROM Locations l
        WHERE l.Location = c.Location
        AND l.Status_Date <= c.Completion
        ORDER BY l.Status_Date DESC
        LIMIT 1
    ) AS Status_Completion
FROM Customers c

示例数据验证 #

针对给出的样例数据，两种写法都会返回完全符合预期的结果：

注意：执行前请先将Registration、Completion、Status_Date三个字段统一转换为日期类型，避免字符串格式日期出现比较逻辑错误。

内容的提问来源于stack exchange，提问作者Mr. Jibz