SQL求助：查找表中与条目B日期最接近的不同值条目A的实现方法

实现方案 #

以下是支持窗口函数的主流数据库（MySQL 8.0+、PostgreSQL、Oracle、SQL Server 等）通用写法，性能更优：

WITH b_list AS (
    -- 提取所有B类条目
    SELECT 日期 AS b_date FROM TAB WHERE 条目 = 'B'
),
a_list AS (
    -- 提取所有A类条目
    SELECT 日期 AS a_date FROM TAB WHERE 条目 = 'A'
)
SELECT b_date, 最接近的A的日期
FROM (
    SELECT 
        b.b_date,
        a.a_date AS 最接近的A的日期,
        -- 按A和B的时间差绝对值升序排序，差值最小的排第一位
        ROW_NUMBER() OVER (
            PARTITION BY b.b_date 
            ORDER BY ABS(TO_TIMESTAMP(a.a_date, 'dd.MM.yyyy HH24:mi:ss') - TO_TIMESTAMP(b.b_date, 'dd.MM.yyyy HH24:mi:ss')) ASC
        ) AS rn
    FROM b_list b
    CROSS JOIN a_list a
) t
WHERE rn = 1;

注意事项 #

1. 日期转换函数适配：
   • 若表中日期字段本身就是 datetime/timestamp 类型，不需要做格式转换，直接替换成字段名即可
   • MySQL 环境下字符串转日期用 STR_TO_DATE(日期, '%d.%m.%Y %H:%i:%s')
   • SQL Server 环境下字符串转日期用 CONVERT(DATETIME, 日期, 104)
2. 特殊场景调整：
   • 若要求只匹配B时间之前的最近A，可去掉ABS()，在ORDER BY前新增筛选条件 WHERE TO_TIMESTAMP(a.a_date, 'dd.MM.yyyy HH24:mi:ss') <= TO_TIMESTAMP(b.b_date, 'dd.MM.yyyy HH24:mi:ss')
   • 若要求只匹配B时间之后的最近A，把上面的<=换成>=即可
   • 若存在两个A和B的时间差完全相等的情况，把ROW_NUMBER()换成RANK()即可返回所有符合条件的A

低版本数据库兼容写法 #

如果你的数据库不支持窗口函数（比如MySQL 5.x及以下版本），可以用子查询聚合实现：

SELECT 
    b.日期 AS b_date,
    a.日期 AS 最接近的A的日期
FROM TAB b
INNER JOIN TAB a ON a.条目 = 'A'
WHERE b.条目 = 'B'
AND ABS(TO_TIMESTAMP(a.日期, 'dd.MM.yyyy HH24:mi:ss') - TO_TIMESTAMP(b.日期, 'dd.MM.yyyy HH24:mi:ss')) = (
    SELECT MIN(ABS(TO_TIMESTAMP(a2.日期, 'dd.MM.yyyy HH24:mi:ss') - TO_TIMESTAMP(b.日期, 'dd.MM.yyyy HH24:mi:ss')))
    FROM TAB a2 WHERE a2.条目 = 'A'
)

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