如何基于REF关联匹配Ops_Date最近的前置Arrival日期（无通用ID）

没问题，这个需求完全可以用纯SQL实现，不需要依赖ETL工具或者存储过程。下面我会给出两种通用的实现方案，适配不同的数据库环境：

方法一：使用窗口函数（推荐，适用于支持窗口函数的数据库） #

这种方法逻辑清晰，可读性强，是现在主流数据库的首选方案：

WITH ranked_matches AS (
    SELECT 
        s.PK1,
        s.REF,
        s.Ops_Date,
        d.PK2,
        d.Arrival,
        -- 按每条Service记录分组，对关联到的Dim记录按Arrival倒序排名
        ROW_NUMBER() OVER (PARTITION BY s.PK1 ORDER BY d.Arrival DESC) AS rank_num
    FROM service s
    LEFT JOIN dim d 
        ON s.REF = d.REF 
        AND d.Arrival < s.Ops_Date -- 只匹配早于Ops_Date的Arrival记录
)
SELECT PK1, REF, Ops_Date, PK2, Arrival
FROM ranked_matches
WHERE rank_num = 1 -- 取排名第一的（最近的日期）
ORDER BY PK1;

逻辑说明： #

1. 先通过LEFT JOIN关联两张表，同时过滤掉Dim表中Arrival晚于等于Service表Ops_Date的记录（题目明确Ops_Date始终晚于Arrival，这个条件可以确保只保留符合时间要求的候选记录）
2. 用ROW_NUMBER()窗口函数，按Service表的主键PK1分组（保证每条Service记录单独处理），对每个分组内的Dim记录按Arrival降序排序，这样最近的日期会被标记为rank_num=1
3. 最后筛选出rank_num=1的记录，就是每条Service记录对应的最近Dim匹配项

方法二：使用NOT EXISTS（适配不支持窗口函数的旧版本数据库，比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用这种子查询的方式实现：

SELECT 
    s.PK1,
    s.REF,
    s.Ops_Date,
    d.PK2,
    d.Arrival
FROM service s
LEFT JOIN dim d 
    ON s.REF = d.REF 
    AND d.Arrival < s.Ops_Date
WHERE NOT EXISTS (
    -- 确保没有其他同REF的Dim记录，Arrival比当前d的Arrival更近且早于Ops_Date
    SELECT 1
    FROM dim d2
    WHERE d2.REF = s.REF
      AND d2.Arrival < s.Ops_Date
      AND d2.Arrival > d.Arrival
)
ORDER BY s.PK1;

逻辑说明： #

通过NOT EXISTS子查询来验证：当前匹配的Dim记录d，是同REF下所有早于Ops_Date的记录中Arrival最大的（也就是最近的），因为如果存在其他更近的记录，这个子查询就会返回结果，当前记录就会被过滤掉。

验证结果 #

这两种方法执行后，都会得到你期望的结果：

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