基于日期匹配或最新值的TableA与TableB关联SQL实现需求

先明确需求 #

咱们要把TableA和TableB做关联，每个CID必须只返回一行，匹配规则分优先级：

1. 优先选：TableA里该CID的Start_dt落在TableB同CID的AreaStart_dt和Area_End_dt区间内的记录
2. 要是没找到区间匹配的，就取TableB里对应CID中AreaStart_dt最新的那条记录

原始表数据（整理排版后） #

TableA

CID  Start_dt  End_dt
1    1/1/18    1/1/3000
2    5/1/18    1/1/3000

TableB

CID  Areaid  AreaStart_dt  Area_End_dt
1    101     1/1/18        1/1/3000
1    102     1/1/17        12/31/17
2    201     4/1/18        4/29/18
2    301     3/1/18        3/30/18

你提供的原始SQL的问题 #

仔细梳理后发现这个SQL有好几处硬伤，肯定跑不出预期结果：

• 字段名笔误：比如A.CID = B.ID，TableB里根本没有ID字段，应该是B.CID；还有A.CLIENTID，原表用的是CID，明显是手滑写错了
• 子查询缺关键字段：只计算了RNK排名，没把Areaid、AreaStart_dt这些关联需要的字段选出来，主查询根本拿不到要的Areaid数据
• 排序逻辑错误：仅按AREA_START_DT DESC排序，没考虑“区间匹配优先”的核心规则，这样会直接把最新日期的记录放前面，不管是否在目标区间内
• ON子句逻辑混乱：把区间匹配和rnk=1用OR直接连接，会导致同一个CID关联出多条不符合要求的记录，没法保证唯一行的要求

优化后的正确SQL #

SELECT 
    A.CID,
    A.Start_dt,
    A.End_dt,
    B.Areaid
FROM TableA A
LEFT JOIN (
    SELECT 
        CID,
        Areaid,
        AreaStart_dt,
        Area_End_dt,
        -- 核心逻辑：先按是否匹配区间排优先级，再按日期降序
        DENSE_RANK() OVER (
            PARTITION BY CID 
            ORDER BY 
                -- 区间匹配的记录标记为0排前面，不匹配的标记为1排后面
                CASE WHEN A.Start_dt BETWEEN AreaStart_dt AND Area_End_dt THEN 0 ELSE 1 END,
                AreaStart_dt DESC
        ) AS RNK
    FROM TableB
) B ON A.CID = B.CID
WHERE B.RNK = 1;

逻辑说明 #

1. 子查询的排序是实现需求的关键：
   • 用CASE语句给区间匹配的记录打0分，不匹配的打1分，排序时0在前，确保优先选择区间内的记录
   • 若同个CID没有区间匹配的记录，就按AreaStart_dt降序排序，取最新的那条
   • 用DENSE_RANK()按CID分组排名，每个CID里符合优先级的记录会拿到RNK=1
2. 主查询关联后，只取RNK=1的记录，保证每个CID仅返回一行
3. 用LEFT JOIN是为了确保TableA里的所有CID都能被返回，哪怕TableB里没有对应数据（如果不需要这个特性，可以改成INNER JOIN）

验证结果 #

执行上述SQL后，会得到你期望的输出：

CID  Start_dt  End_dt     Areaid
1    1/1/18    1/1/3000   101
2    5/1/18    1/1/3000   201

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