SQL Server中使用CASE WHEN关联两表报错问题求助

我来帮你搞定这个关联表的问题，先把需求再理一遍：要关联tmp1和tmp2，当tmp1的A字段不为空时，直接用tmp1.A=tmp2.A AND tmp1.B=tmp2.B AND tmp1.C=tmp2.C匹配；当tmp1.A为空时，先取tmp2中相同B、C分组下的最小A值，再用这个最小A+B+C来匹配tmp2，最终返回tmp1.* + tmp2.D的结果。

方案一：用CTE预处理最小A值（推荐） #

先用CTE（公共表达式）把tmp2里每个(B,C)分组对应的最小A值提前算出来，再分条件关联，逻辑清晰还能保证性能：

-- 预处理tmp2中每个(B,C)分组的最小A值
WITH tmp2_group_min AS (
    SELECT 
        B,
        C,
        MIN(A) AS min_A
    FROM tmp2
    GROUP BY B, C
)
SELECT 
    t1.A,
    t1.B,
    t1.C,
    t2.D
FROM tmp1 t1
-- 先关联到预处理的最小A值表
JOIN tmp2_group_min tgm ON t1.B = tgm.B AND t1.C = tgm.C
-- 分条件匹配tmp2
JOIN tmp2 t2 ON 
    -- 情况1：tmp1.A不为空时直接匹配A、B、C
    (t1.A IS NOT NULL AND t1.A = t2.A AND t1.B = t2.B AND t1.C = t2.C)
    -- 情况2：tmp1.A为空时用预处理的最小A匹配
    OR 
    (t1.A IS NULL AND tgm.min_A = t2.A AND t1.B = t2.B AND t1.C = t2.C);

方案二：直接嵌套子查询的简化写法 #

如果不想用CTE，也可以直接在关联条件里嵌套子查询获取最小A值，不过数据量大时性能可能不如CTE：

SELECT 
    t1.A,
    t1.B,
    t1.C,
    t2.D
FROM tmp1 t1
JOIN tmp2 t2 ON 
    (t1.A IS NOT NULL AND t1.A = t2.A AND t1.B = t2.B AND t1.C = t2.C)
    OR 
    (t1.A IS NULL 
     AND t2.A = (SELECT MIN(A) FROM tmp2 WHERE B = t1.B AND C = t1.C) 
     AND t1.B = t2.B 
     AND t1.C = t2.C);

最终结果验证 #

执行上面的SQL后，会得到你期望的结果：

为啥之前的CASE WHEN会出错？ #

很多人会尝试把CASE WHEN直接塞进ON子句，但如果逻辑构造不对（比如只返回单个字段的匹配，没覆盖完整的关联条件），就会导致关联失败。上面的写法用明确的OR分支分情况处理，逻辑直观还容易排查问题。

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