多表关联下为每条记录分配唯一的最近后续匹配日期

需求说明 #

需要为Table1中的每个ID匹配Table2中满足以下条件的ClosestDt：

• 该日期大于对应ID的JoiningDt
• 每个ClosestDt只能分配给一个ID（例如ID2不能使用ID1已占用的07-Apr-2024，需选择下一个符合条件的08-Apr-2024）

数据表结构与数据 #

Table1 #

Table2 #

预期输出 #

已尝试方法及问题 #

• 左外连接：执行以下SQL后得到重复匹配结果，无法筛选出唯一的最近日期：

select t1.ID ,t1.JoiningDt, t1.DocNum, (t2.ClosestDt)
from #Table1 t1
left join #Table2 t2 on
    t1.DocNum = t2.DocNum
    and t2.ClosestDt > t1.JoiningDt

• ROW_NUMBER()函数：尝试用窗口函数排序，但无法处理日期被占用后需要跳过的场景，导致ID2仍会匹配到已被ID1占用的07-Apr-2024。

解决方案 #

可以通过给两张表按DocNum分组排序，再匹配序号并筛选最近日期的方式实现，以下是适用于SQL Server 2022+（支持QUALIFY）的代码：

WITH ranked_ids AS (
    -- 给Table1按DocNum分组，按JoiningDt排序生成序号
    SELECT 
        ID, JoiningDt, DocNum,
        ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY JoiningDt) AS id_rank
    FROM #Table1
),
ranked_dates AS (
    -- 给Table2按DocNum分组，按ClosestDt排序生成序号
    SELECT 
        DocNum, ClosestDt,
        ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY ClosestDt) AS dt_rank
    FROM #Table2
)
SELECT 
    r.ID, r.JoiningDt, r.DocNum, d.ClosestDt
FROM ranked_ids r
INNER JOIN ranked_dates d 
    ON r.DocNum = d.DocNum
    AND d.dt_rank >= r.id_rank  -- 确保先处理的ID占用更早的符合条件日期
    AND d.ClosestDt > r.JoiningDt
QUALIFY ROW_NUMBER() OVER (PARTITION BY r.ID ORDER BY d.ClosestDt) = 1  -- 筛选每个ID的最近日期
ORDER BY r.ID;

如果使用不支持QUALIFY的SQL版本，可以改用子查询实现相同逻辑：

WITH ranked_ids AS (
    SELECT 
        ID, JoiningDt, DocNum,
        ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY JoiningDt) AS id_rank
    FROM #Table1
),
ranked_dates AS (
    SELECT 
        DocNum, ClosestDt,
        ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY ClosestDt) AS dt_rank
    FROM #Table2
),
matched_candidates AS (
    SELECT 
        r.ID, r.JoiningDt, r.DocNum, d.ClosestDt,
        ROW_NUMBER() OVER (PARTITION BY r.ID ORDER BY d.ClosestDt) AS rn
    FROM ranked_ids r
    INNER JOIN ranked_dates d 
        ON r.DocNum = d.DocNum
        AND d.dt_rank >= r.id_rank
        AND d.ClosestDt > r.JoiningDt
)
SELECT ID, JoiningDt, DocNum, ClosestDt
FROM matched_candidates
WHERE rn = 1
ORDER BY ID;

方案说明 #

1. 先对Table1按DocNum分组，根据JoiningDt为每个ID生成序号，确保早入职的ID优先分配日期；
2. 对Table2按DocNum分组，根据ClosestDt为每个日期生成序号；
3. 匹配相同DocNum的行，且日期序号不小于ID序号（避免后处理的ID占用先处理ID的日期），同时确保日期大于入职日期；
4. 最后为每个ID筛选出最近的符合条件的日期，保证每个日期只被分配一次。

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