如何关联含多匹配记录的两张表并实现最优日期匹配？

看来你是想给同ID下的记录做日期的精准匹配——要找t2里最贴近但不晚于t1日期的那条，没匹配到就留空对吧？你的原SQL确实没法实现这个需求：inner join会直接过滤掉没有符合条件的t2记录，而且如果t2有多条满足t2.date <= t1.date的记录，还会返回多条重复结果，完全达不到“最优匹配”的要求。

下面给你几个不同数据库都适用的解决方案，按需选就行：

方法1：窗口函数（通用MySQL 8+、PostgreSQL、SQL Server等） #

先把所有符合ID和日期条件的记录关联起来，再用ROW_NUMBER()窗口函数给每个t1的记录筛选出最近的那条t2记录：

WITH matched_data AS (
    SELECT 
        t1.id,
        t1.date AS t1_date,
        t2.date AS t2_date,
        -- 给每个t1的记录对应的t2记录按日期倒序排，最近的排第1
        ROW_NUMBER() OVER (PARTITION BY t1.id, t1.date ORDER BY t2.date DESC) AS rn
    FROM t1
    LEFT JOIN t2 
        ON t1.id = t2.id 
        AND t2.date <= t1.date
)
SELECT 
    id,
    t1_date,
    -- 只保留排名第1的t2日期，没匹配的就是NULL
    CASE WHEN rn = 1 THEN t2_date ELSE NULL END AS t2_date
FROM matched_data
WHERE rn = 1 OR rn IS NULL -- 确保t1的所有记录都保留，哪怕没匹配
ORDER BY id, t1_date;

方法2：LATERAL JOIN（PostgreSQL、Oracle 12c+等） #

这种写法更直观，相当于给t1的每条记录单独查一次t2，直接取最近的那条：

SELECT 
    t1.id,
    t1.date AS t1_date,
    t2.date AS t2_date
FROM t1
LEFT JOIN LATERAL (
    SELECT date 
    FROM t2 
    WHERE t2.id = t1.id AND t2.date <= t1.date
    ORDER BY date DESC
    LIMIT 1 -- 只拿最近的一条
) t2 ON true
ORDER BY t1.id, t1.date;

方法3：APPLY（SQL Server专属） #

和上面的LATERAL JOIN逻辑一样，SQL Server用OUTER APPLY来实现单条记录的关联查询：

SELECT 
    t1.id,
    t1.date AS t1_date,
    t2.date AS t2_date
FROM t1
OUTER APPLY (
    SELECT TOP 1 date 
    FROM t2 
    WHERE t2.id = t1.id AND t2.date <= t1.date
    ORDER BY date DESC
) t2
ORDER BY t1.id, t1.date;

结果验证 #

用你给的示例数据测试：

• t1的记录：3/15、4/11、4/16、4/21
• t2的记录：4/10、4/15、4/20

执行后会得到你想要的结果：

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