SQL Server 2014三张百万级数据表的数据差异排查需求

针对你在SQL Server 2014中处理三张百万级数据表差异排查的需求，我整理了两种高效的实现思路，既能覆盖你要的两类差异（键组合缺失、val值不一致），又能保留不需要比对的附加列add_cd和add_ef，同时兼顾大数据量的查询性能：

首先明确核心需求：三张表的共同键是k1和k2，需要找出两类差异：一是键组合未同时存在于三张表，二是键组合存在但val值不一致；结果需保留add_cd、add_ef但不比对它们。

方案一：合并数据后分组统计（统一输出所有差异） #

这个方案通过合并三张表的数据，对每个键组合做统计，一次性筛选出两类差异，适合需要把所有差异放在同一结果集的场景：

-- 先创建覆盖索引提升性能（百万级数据必备）
-- CREATE NONCLUSTERED INDEX IX_Table1_k1_k2 ON Table1(k1, k2) INCLUDE(val, add_cd, add_ef);
-- CREATE NONCLUSTERED INDEX IX_Table2_k1_k2 ON Table2(k1, k2) INCLUDE(val, add_cd, add_ef);
-- CREATE NONCLUSTERED INDEX IX_Table3_k1_k2 ON Table3(k1, k2) INCLUDE(val, add_cd, add_ef);

WITH CombinedData AS (
    -- 合并三张表数据，标记来源表
    SELECT k1, k2, val, add_cd, add_ef, 'Table1' AS source_table
    FROM Table1
    UNION ALL
    SELECT k1, k2, val, add_cd, add_ef, 'Table2' AS source_table
    FROM Table2
    UNION ALL
    SELECT k1, k2, val, add_cd, add_ef, 'Table3' AS source_table
    FROM Table3
),
KeyStats AS (
    -- 统计每个键组合的关键指标：涉及表数量、val不同值数量、各表是否存在该键
    SELECT 
        k1, k2,
        COUNT(DISTINCT source_table) AS table_count,
        COUNT(DISTINCT val) AS val_distinct_count,
        CASE WHEN EXISTS(SELECT 1 FROM Table1 t1 WHERE t1.k1 = cd.k1 AND t1.k2 = cd.k2) THEN 1 ELSE 0 END AS has_table1,
        CASE WHEN EXISTS(SELECT 1 FROM Table2 t2 WHERE t2.k1 = cd.k1 AND t2.k2 = cd.k2) THEN 1 ELSE 0 END AS has_table2,
        CASE WHEN EXISTS(SELECT 1 FROM Table3 t3 WHERE t3.k1 = cd.k1 AND t3.k2 = cd.k2) THEN 1 ELSE 0 END AS has_table3
    FROM CombinedData cd
    GROUP BY k1, k2
)
-- 筛选差异并输出结果
SELECT 
    cd.k1, cd.k2, cd.val, cd.add_cd, cd.add_ef, cd.source_table,
    CASE 
        WHEN ks.table_count < 3 THEN 
            'Missing in: ' + 
            CASE WHEN ks.has_table1 = 0 THEN 'Table1' ELSE '' END +
            CASE WHEN ks.has_table2 = 0 AND ks.has_table1 = 0 THEN ', Table2' WHEN ks.has_table2 = 0 THEN 'Table2' ELSE '' END +
            CASE WHEN ks.has_table3 = 0 AND (ks.has_table1 = 0 OR ks.has_table2 = 0) THEN ', Table3' WHEN ks.has_table3 = 0 THEN 'Table3' ELSE '' END
        WHEN ks.val_distinct_count > 1 THEN 'Value mismatch across tables'
    END AS discrepancy_type
FROM CombinedData cd
JOIN KeyStats ks ON cd.k1 = ks.k1 AND cd.k2 = ks.k2
WHERE 
    ks.table_count < 3 -- 键组合未在所有表存在
    OR ks.val_distinct_count > 1 -- val值不一致
ORDER BY cd.k1, cd.k2, cd.source_table;

方案二：拆分两类差异分别查询（逻辑更清晰，适合分步排查） #

如果你希望分开查看两类差异，或者想优化特定场景的性能，可以拆分查询：

1. 查找未同时存在于三张表的键组合

SELECT 
    COALESCE(t1.k1, t2.k1, t3.k1) AS k1,
    COALESCE(t1.k2, t2.k2, t3.k2) AS k2,
    -- 输出各表对应字段，NULL表示该表无此键组合
    t1.val AS val_table1, t1.add_cd AS add_cd_table1, t1.add_ef AS add_ef_table1,
    t2.val AS val_table2, t2.add_cd AS add_cd_table2, t2.add_ef AS add_ef_table2,
    t3.val AS val_table3, t3.add_cd AS add_cd_table3, t3.add_ef AS add_ef_table3,
    -- 标记缺失的表
    'Missing in: ' + 
    CASE WHEN t1.k1 IS NULL THEN 'Table1' ELSE '' END +
    CASE WHEN t2.k1 IS NULL AND t1.k1 IS NULL THEN ', Table2' WHEN t2.k1 IS NULL THEN 'Table2' ELSE '' END +
    CASE WHEN t3.k1 IS NULL AND (t1.k1 IS NULL OR t2.k1 IS NULL) THEN ', Table3' WHEN t3.k1 IS NULL THEN 'Table3' ELSE '' END AS discrepancy_type
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.k1 = t2.k1 AND t1.k2 = t2.k2
FULL OUTER JOIN Table3 t3 ON COALESCE(t1.k1, t2.k1) = t3.k1 AND COALESCE(t1.k2, t2.k2) = t3.k2
WHERE t1.k1 IS NULL OR t2.k1 IS NULL OR t3.k1 IS NULL;

2. 查找键组合存在但val值不一致的记录

SELECT 
    t1.k1, t1.k2,
    t1.val AS val_table1, t1.add_cd AS add_cd_table1, t1.add_ef AS add_ef_table1,
    t2.val AS val_table2, t2.add_cd AS add_cd_table2, t2.add_ef AS add_ef_table2,
    t3.val AS val_table3, t3.add_cd AS add_cd_table3, t3.add_ef AS add_ef_table3,
    'Value mismatch across tables' AS discrepancy_type
FROM Table1 t1
JOIN Table2 t2 ON t1.k1 = t2.k1 AND t1.k2 = t2.k2
JOIN Table3 t3 ON t1.k1 = t3.k1 AND t1.k2 = t3.k2
WHERE 
    -- 考虑val为NULL的情况，避免漏判
    (t1.val <> t2.val OR (t1.val IS NULL AND t2.val IS NOT NULL) OR (t1.val IS NOT NULL AND t2.val IS NULL))
    OR (t1.val <> t3.val OR (t1.val IS NULL AND t3.val IS NOT NULL) OR (t1.val IS NOT NULL AND t3.val IS NULL))
    OR (t2.val <> t3.val OR (t2.val IS NULL AND t3.val IS NOT NULL) OR (t2.val IS NOT NULL AND t3.val IS NULL));

如果需要合并两类结果，只需在两个查询之间加上UNION ALL即可。

性能优化建议 #

1. 索引优化：必须为三张表创建(k1,k2)的复合索引，并包含val、add_cd、add_ef，这样查询可以直接走覆盖索引，避免回表读取数据，大幅提升百万级数据的查询速度。
2. 分批处理：如果数据量极大，一次性查询返回结果太慢，可以按k1范围分批查询（比如WHERE k1 BETWEEN 'A' AND 'M'），分多次获取结果。
3. 避免不必要排序：如果不需要排序，可以去掉ORDER BY子句，进一步提升性能。

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