SQL Server左外连接中CASE表达式未匹配首个WHEN的问题

我明白你困惑的点——你以为ON子句里的CASE会像编程语言里的switch-case一样，找到第一个匹配的条件就停止，只返回那一行，但实际上却返回了所有满足任意WHEN条件的行。这其实是对CASE表达式在JOIN逻辑中的作用理解有偏差，我来给你拆解一下：

首先，CASE表达式的“短路求值”是针对单个行的，不是针对整个JOIN结果集的。也就是说，对于子查询CB里的每一行，数据库都会单独计算CASE的值：如果该行满足第一个WHEN，就返回1；如果不满足，再判断第二个WHEN，满足就返回1；否则返回0。只要CASE结果是1，这一行就会和OA的行进行连接。所以当CB里有两行分别满足两个WHEN条件时，就会生成两条连接后的结果行，这不是CASE的问题，是JOIN的匹配逻辑导致的。

你的原始SQL中，ON子句本质上和直接写ON (条件1) OR (条件2)完全等价，自然会返回所有符合任一条件的行。

那怎么实现“只取首个匹配条件的行”的需求呢？这里有两种常用的解决方案：

方案1：用窗口函数按优先级排序，取第一行 #

我们可以给CB中的行按匹配优先级打分，然后用ROW_NUMBER()给每个客户的行排序，最后只保留优先级最高的那一行：

SELECT 
    OA.MILL_ORDER_NUMBER 
    ,OA.SHORTY_NAME 
    ,OA.PRIMARY_DEST 
    ,OA.ALT_DESTINATION 
    ,CB.CDE_CNSUM_LOC as CB4V_CNSUM_LOC 
    ,CB.CDE_DEST 
    ,CB.NAM_CUST_SHTY 
FROM HLFOR01A OA 
LEFT JOIN (
    SELECT 
        CDE_CNSUM_LOC, 
        CDE_DEST, 
        NAM_CUST_SHTY,
        -- 定义优先级：满足第一个条件的行优先级为1，第二个为2
        CASE 
            WHEN substring(CDE_DEST, 1, 1) < 'A' THEN 1 
            ELSE 2 
        END AS priority,
        -- 按优先级排序，优先级相同则按CDE_DEST升序（对应第二个条件的min逻辑）
        ROW_NUMBER() OVER (
            PARTITION BY NAM_CUST_SHTY 
            ORDER BY 
                CASE 
                    WHEN substring(CDE_DEST, 1, 1) < 'A' THEN 1 
                    ELSE 2 
                END,
                CDE_DEST
        ) AS rn
    FROM CSAR_CB4V0023
) CB ON OA.SHORTY_NAME = CB.NAM_CUST_SHTY 
    AND (
        -- 优先匹配第一个条件的行
        (OA.PRIMARY_DEST = CB.CDE_DEST AND CB.priority = 1)
        -- 如果没有匹配到第一个条件，就取该客户优先级最高的第二个条件行
        OR (CB.rn = 1 AND CB.priority = 2 AND NOT EXISTS (
            SELECT 1 FROM CSAR_CB4V0023 dd 
            WHERE dd.NAM_CUST_SHTY = OA.SHORTY_NAME 
              AND dd.CDE_DEST = OA.PRIMARY_DEST 
              AND substring(dd.CDE_DEST, 1, 1) < 'A'
        ))
    )
WHERE OA.MILL_ORDER_NUMBER = '84220631'

方案2：分层LEFT JOIN，先匹配高优先级条件 #

这种方式更直观：先尝试用第一个条件连接，如果没匹配到，再用第二个条件连接，最后用COALESCE取有效数据：

SELECT 
    OA.MILL_ORDER_NUMBER 
    ,OA.SHORTY_NAME 
    ,OA.PRIMARY_DEST 
    ,OA.ALT_DESTINATION 
    ,COALESCE(CB1.CDE_CNSUM_LOC, CB2.CDE_CNSUM_LOC) as CB4V_CNSUM_LOC 
    ,COALESCE(CB1.CDE_DEST, CB2.CDE_DEST) as CDE_DEST 
    ,COALESCE(CB1.NAM_CUST_SHTY, CB2.NAM_CUST_SHTY) as NAM_CUST_SHTY 
FROM HLFOR01A OA 
-- 第一步：匹配第一个高优先级条件
LEFT JOIN CSAR_CB4V0023 CB1 
    ON OA.SHORTY_NAME = CB1.NAM_CUST_SHTY 
    AND OA.PRIMARY_DEST = CB1.CDE_DEST 
    AND substring(OA.PRIMARY_DEST, 1, 1) < 'A'
-- 第二步：只有当第一步没匹配到，才匹配第二个条件
LEFT JOIN CSAR_CB4V0023 CB2 
    ON OA.SHORTY_NAME = CB2.NAM_CUST_SHTY 
    AND CB2.CDE_DEST = (
        SELECT min(dd.CDE_DEST) 
        FROM CSAR_CB4V0023 dd 
        WHERE dd.NAM_CUST_SHTY = OA.SHORTY_NAME
    )
    AND CB1.CDE_DEST IS NULL -- 确保第一个条件没匹配到才走这里
WHERE OA.MILL_ORDER_NUMBER = '84220631'

这两种方案都能实现你想要的“只取首个满足条件的行”的效果，你可以根据自己数据库的支持情况和性能需求选择。

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