如何编写SQL校验三表关联下a.cp_id字段加载是否正确

核心校验逻辑 #

三张表的关联关系为：a与b通过公共字段tr_id关联，b与c通过公共字段cde关联。校验的核心思路是逐行计算cp_id的规则预期值，再和表中实际存储的值比对，找出不一致的异常记录：

• 当关联得到的b.cde = 'some_value'时，cp_id预期值为对应关联到的c.value
• 其余所有场景（包括b表无匹配tr_id、c表无匹配cde、b.cde不等于指定值），cp_id预期值为NULL

校验SQL语句 #

用左连接关联三张表，避免遗漏无匹配关联记录的异常数据，注意NULL值不能用=/!=判断，要单独写判断逻辑：

SELECT 
    a.tr_id,
    a.cp_id AS actual_cp_id,
    CASE WHEN b.cde = 'some_value' THEN c.value ELSE NULL END AS expected_cp_id,
    b.cde AS b_matched_cde,
    c.value AS c_matched_value
FROM a
LEFT JOIN b ON a.tr_id = b.tr_id
LEFT JOIN c ON b.cde = c.cde
WHERE
    -- 场景1：预期为NULL，但实际存了非NULL值
    (CASE WHEN b.cde = 'some_value' THEN c.value ELSE NULL END IS NULL AND a.cp_id IS NOT NULL)
    OR
    -- 场景2：预期为非NULL值，但实际为NULL或者值不匹配
    (CASE WHEN b.cde = 'some_value' THEN c.value ELSE NULL END IS NOT NULL AND (a.cp_id IS NULL OR a.cp_id <> c.value))
;

结果说明 #

• 如果上述查询返回空结果，说明当前a表的cp_id字段完全符合给定的加载规则
• 如果返回记录，每一行都是加载错误的数据：actual_cp_id是表中实际存储的错误值，expected_cp_id是按照规则应该写入的正确值，后两个字段可以帮你快速定位关联环节的问题

注意事项 #

• 不要用内连接做关联，否则会漏掉「a表tr_id在b表无匹配」「b表cde在c表无匹配」这两类场景下的异常数据
• 如果b表同一个tr_id存在多条重复记录、或者c表同一个cde存在多条重复记录，建议先对子查询去重后再关联，避免关联产生笛卡尔积导致校验结果不准，例如b表可以替换为(SELECT DISTINCT tr_id, cde FROM b) b。

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