SQL实现：为表A每行匹配同ID下最近的表B特殊日期

这是SQL里非常典型的「按维度分组后基于时间找最近匹配记录」的As-Of Join场景，不需要复杂的多表嵌套，根据你使用的数据库版本选对应写法即可，所有写法都可以直接产出你需要的新表。

通用高性能写法（支持MySQL8.0+、PostgreSQL、Hive、Spark SQL、SQL Server等所有支持窗口函数的数据库） #

这个写法性能远高于传统join+group by取max的方案，尤其适合你提到的表A覆盖ID全量日期、数据规模大的场景：

-- 通用CTAS建表语法，不同数据库建表关键字可按需调整
CREATE TABLE target_table AS
WITH combine_records AS (
    -- 标记表A的记录，特殊日期字段先置空
    SELECT 
        ID,
        Date,
        NULL AS special_date,
        0 AS source_flag
    FROM table_A
    UNION ALL
    -- 标记表B的记录，特殊日期取自身日期值
    SELECT
        ID,
        Date,
        Date AS special_date,
        1 AS source_flag
    FROM table_B
)
SELECT
    ID,
    Date,
    most_recent_special_date
FROM (
    SELECT
        ID,
        Date,
        source_flag,
        -- 按ID分区，按日期升序、同日期下表B记录优先排序
        -- 取当前窗口之前最近的非空特殊日期
        LAST_VALUE(special_date IGNORE NULLS) OVER (
            PARTITION BY ID 
            ORDER BY Date, source_flag DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS most_recent_special_date
    FROM combine_records
) filtered
-- 只保留原表A的记录
WHERE source_flag = 0;

注意：如果使用SQLite等不支持IGNORE NULLS语法的数据库，可使用下面的低版本兼容写法。

低版本兼容写法（不支持窗口函数的场景） #

逻辑非常直接：先按ID关联所有符合「不晚于当前行日期」的表B记录，再分组取最大的特殊日期，就是距离当前行最近的日期：

CREATE TABLE target_table AS
SELECT
    a.ID,
    a.Date,
    MAX(b.Date) AS most_recent_special_date
FROM table_A a
LEFT JOIN table_B b
ON a.ID = b.ID AND b.Date <= a.Date
GROUP BY a.ID, a.Date;

这个写法逻辑简单易理解，缺点是当两个表数据量都很大时，join产生的中间结果会导致查询性能很差。

特定数据库专属高效写法 #

• PostgreSQL 可以用LATERAL JOIN实现，性能比通用窗口函数写法更好：

CREATE TABLE target_table AS
SELECT
    a.ID,
    a.Date,
    b.Date AS most_recent_special_date
FROM table_A a
LEFT JOIN LATERAL (
    SELECT Date FROM table_B b
    WHERE b.ID = a.ID AND b.Date <= a.Date
    ORDER BY b.Date DESC
    LIMIT 1
) b ON true;

• Hive/Spark等大数据引擎可以直接用原生ASOF JOIN语法，是引擎专门为这类时间最近匹配场景优化的，性能最优：

CREATE TABLE target_table AS
SELECT
    a.ID,
    a.Date,
    b.Date AS most_recent_special_date
FROM table_A a
LEFT ASOF JOIN table_B b
ON a.ID = b.ID AND a.Date >= b.Date;

结果校验 #

用你给出的示例数据运行上述任意写法，输出结果完全匹配你的预期：

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