MySQL如何基于最接近匹配的时间戳实现JOIN关联查询

核心逻辑是放弃精确等值匹配，改为计算两个时间戳的绝对差值，为每一条user_subscription_history记录取差值最小的1条关联交易记录即可，以下是不同场景的可落地写法：

推荐写法（支持MySQL 8.0.14+、PostgreSQL等支持LATERAL语法的数据库） #

性能最好，逻辑最直观：

SELECT a.*, b.* -- 按需保留需要的字段，不需要b的字段可直接写a.*
FROM `user_subscription_history` a
LEFT JOIN LATERAL (
    SELECT *
    FROM `transaction` b
    -- 【性能优化强烈建议加】根据业务限定匹配的时间范围，避免全表扫描，比如仅匹配a记录时间前后1天内的交易
    -- WHERE b.tran_date >= a.date_time - INTERVAL 1 DAY
    --   AND b.tran_date <= a.date_time + INTERVAL 1 DAY
    ORDER BY ABS(TIMESTAMPDIFF(SECOND, b.tran_date, a.date_time)) ASC
    -- 同时间差时保证结果稳定，可追加排序规则，比如按交易id升序：, b.id ASC
    LIMIT 1
) b ON true;

兼容窗口函数但不支持LATERAL的场景（MySQL 8.0~8.0.13、SQL Server等） #

SELECT a.*, match_b.*
FROM `user_subscription_history` a
LEFT JOIN (
    SELECT 
        b.*,
        a.id AS a_id, -- 替换为user_subscription_history表的实际主键字段名
        ROW_NUMBER() OVER (
            PARTITION BY a.id
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, b.tran_date, a.date_time)) ASC
            -- 同差值追加稳定排序规则，比如,b.tran_date ASC
        ) AS rn
    FROM `user_subscription_history` a
    LEFT JOIN `transaction` b
    -- 同样建议加时间范围过滤优化性能
    -- ON b.tran_date BETWEEN DATE_SUB(a.date_time, INTERVAL 1 DAY) AND DATE_ADD(a.date_time, INTERVAL 1 DAY)
) match_b ON a.id = match_b.a_id AND match_b.rn = 1;

老版本兼容写法（MySQL 5.x等不支持窗口函数/LATERAL的版本） #

仅适合数据量较小的场景，性能较差：

SELECT a.*, b.*
FROM `user_subscription_history` a
LEFT JOIN `transaction` b 
  ON ABS(TIMESTAMPDIFF(SECOND, b.tran_date, a.date_time)) = (
    SELECT MIN(ABS(TIMESTAMPDIFF(SECOND, b2.tran_date, a.date_time)))
    FROM `transaction` b2
    -- 同样建议加时间范围过滤
    -- WHERE b2.tran_date BETWEEN DATE_SUB(a.date_time, INTERVAL 1 DAY) AND DATE_ADD(a.date_time, INTERVAL 1 DAY)
);

• 不同数据库计算时间戳差值的函数有区别：PostgreSQL可直接用ABS(EXTRACT(EPOCH FROM (b.tran_date - a.date_time)))计算秒级差值，SQL Server用ABS(DATEDIFF(SECOND, a.date_time, b.tran_date))，核心排序逻辑不变。
• 若存在多条交易记录和当前订阅记录的时间差完全相等，上述前两种写法默认只返回1条，建议追加固定排序规则避免结果随机；第三种写法会返回所有差值最小的记录，需要去重可额外加GROUP BY逻辑。
• 千万不要不加时间范围过滤直接全表关联交易表，数据量超过10万条后查询延迟会非常高。

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