如何编写SQL Server查询？实现两表差异对比并生成结果表

看起来你需要的是精准对比两张表的记录存在性和字段值差异，并将每种差异单独生成一条记录存入结果表。之前用UNION ALL出现重复和异常，大概率是因为没有明确区分不同类型的差异场景，或者关联条件没处理好NULL值的问题。

核心分析 #

首先我们需要确定记录的唯一关联键：从你的数据来看，应该用pitem_id, prev_id, citem_id, crev_id这四个字段的组合来匹配两张表的记录（这四个字段共同标识一条唯一的产品明细）。

接下来我们需要覆盖四类差异场景：

1. 源表有记录，但目标表完全没有匹配的记录（关联键不匹配）
2. 源表和目标表有匹配记录，但qty字段值不一致
3. 源表和目标表有匹配记录，但check_no字段值不一致
4. 源表和目标表有匹配记录，但status字段值不一致

完整SQL实现 #

下面的查询会分场景处理差异，并用UNION ALL合并结果，不会产生重复数据：

-- 场景1：源表存在但目标表无匹配的记录
SELECT
    sp.pitem_id,
    sp.prev_id,
    sp.citem_id,
    sp.crev_id,
    'pitemid ,prev_id not found in target' AS Validation_error,
    'pitem_id, prev_id' AS validation_column,
    CONCAT(COALESCE(sp.pitem_id, ''), ', ', COALESCE(sp.prev_id, '')) AS Source_value,
    NULL AS target_value
FROM source_product sp
LEFT JOIN target_product tp
    ON sp.pitem_id = tp.pitem_id
    AND COALESCE(sp.prev_id, '') = COALESCE(tp.prev_id, '')
    AND COALESCE(sp.citem_id, '') = COALESCE(tp.citem_id, '')
    AND COALESCE(sp.crev_id, '') = COALESCE(tp.crev_id, '')
WHERE tp.pitem_id IS NULL

UNION ALL

-- 场景2：qty字段不匹配
SELECT
    sp.pitem_id,
    sp.prev_id,
    sp.citem_id,
    sp.crev_id,
    'qty mismatch' AS Validation_error,
    'qty' AS validation_column,
    CAST(COALESCE(sp.qty, 0) AS VARCHAR) AS Source_value,
    CAST(COALESCE(tp.qty, 0) AS VARCHAR) AS target_value
FROM source_product sp
INNER JOIN target_product tp
    ON sp.pitem_id = tp.pitem_id
    AND COALESCE(sp.prev_id, '') = COALESCE(tp.prev_id, '')
    AND COALESCE(sp.citem_id, '') = COALESCE(tp.citem_id, '')
    AND COALESCE(sp.crev_id, '') = COALESCE(tp.crev_id, '')
WHERE COALESCE(sp.qty, 0) != COALESCE(tp.qty, 0)

UNION ALL

-- 场景3：check_no字段不匹配
SELECT
    sp.pitem_id,
    sp.prev_id,
    sp.citem_id,
    sp.crev_id,
    'check_no mismatch' AS Validation_error,
    'check_no' AS validation_column,
    CAST(COALESCE(sp.check_no, 0) AS VARCHAR) AS Source_value,
    CAST(COALESCE(tp.check_no, 0) AS VARCHAR) AS target_value
FROM source_product sp
INNER JOIN target_product tp
    ON sp.pitem_id = tp.pitem_id
    AND COALESCE(sp.prev_id, '') = COALESCE(tp.prev_id, '')
    AND COALESCE(sp.citem_id, '') = COALESCE(tp.citem_id, '')
    AND COALESCE(sp.crev_id, '') = COALESCE(tp.crev_id, '')
WHERE COALESCE(sp.check_no, 0) != COALESCE(tp.check_no, 0)

UNION ALL

-- 场景4：status字段不匹配
SELECT
    sp.pitem_id,
    sp.prev_id,
    sp.citem_id,
    sp.crev_id,
    'status mismatch' AS Validation_error,
    'status' AS validation_column,
    COALESCE(sp.status, '') AS Source_value,
    COALESCE(tp.status, '') AS target_value
FROM source_product sp
INNER JOIN target_product tp
    ON sp.pitem_id = tp.pitem_id
    AND COALESCE(sp.prev_id, '') = COALESCE(tp.prev_id, '')
    AND COALESCE(sp.citem_id, '') = COALESCE(tp.citem_id, '')
    AND COALESCE(sp.crev_id, '') = COALESCE(tp.crev_id, '')
WHERE COALESCE(sp.status, '') != COALESCE(tp.status, '')

关键细节说明 #

• 用COALESCE处理NULL值：因为NULL和任何值比较都会返回UNKNOWN，所以我们把NULL转换成空字符串或0（根据字段类型），确保比较逻辑正确。
• 分场景独立查询：每个差异场景单独编写查询，保证每种差异只生成一条记录，避免重复。
• 统一字段类型：把数值类型（比如qty、check_no）转换成字符串，确保Source_value和target_value字段类型一致。

如果需要把结果存入final_product表，只需要在整个查询前加上INSERT INTO final_product (pitem_id, prev_id, citem_id, crev_id, Validation_error, validation_column, Source_value, target_value)即可。

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