如何从3张及以上同结构表中筛选基于唯一组合列的缺失值？

嘿，我来给你解决这个三表A-B唯一组合对比的问题，刚好在这类数据校验场景上有不少经验。下面分步骤给你讲清楚怎么实现，还有不同数据库的适配代码：

我们要完成三个关键步骤：

1. 生成所有可能的A-B组合全集：从三张表中提取不重复的Region-Customer_ID组合，确保不会漏掉任何需要对比的项
2. 定位每张表的缺失组合：通过左连接匹配全集和单表，筛选出匹配失败的记录就是该表缺失的组合
3. 统计+格式聚合：给每个缺失组合生成序号（对应你示例里的001）、统计单表总缺失数，最后用字符串聚合函数拼成要求的格式

以下代码假设三张表名为Table1、Table2、Table3，唯一组合列为Region和Customer_ID。

Oracle 版本（原生支持LISTAGG） #

WITH all_combinations AS (
    -- 生成所有不重复的A-B组合全集
    SELECT Region, Customer_ID FROM Table1
    UNION
    SELECT Region, Customer_ID FROM Table2
    UNION
    SELECT Region, Customer_ID FROM Table3
),
missing_details AS (
    -- 找出每张表的缺失组合，生成序号和总缺失数
    SELECT 
        'Table 1' AS table_name,
        -- 给每个缺失组合补零生成3位序号（对应示例的001、002）
        LPAD(ROW_NUMBER() OVER (PARTITION BY 'Table 1' ORDER BY Region, Customer_ID), 3, '0') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 1') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table1 t1 ON ac.Region = t1.Region AND ac.Customer_ID = t1.Customer_ID
    WHERE t1.Region IS NULL -- 左连接后为空的就是缺失项
    UNION ALL
    SELECT 
        'Table 2' AS table_name,
        LPAD(ROW_NUMBER() OVER (PARTITION BY 'Table 2' ORDER BY Region, Customer_ID), 3, '0') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 2') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table2 t2 ON ac.Region = t2.Region AND ac.Customer_ID = t2.Customer_ID
    WHERE t2.Region IS NULL
    UNION ALL
    SELECT 
        'Table 3' AS table_name,
        LPAD(ROW_NUMBER() OVER (PARTITION BY 'Table 3' ORDER BY Region, Customer_ID), 3, '0') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 3') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table3 t3 ON ac.Region = t3.Region AND ac.Customer_ID = t3.Customer_ID
    WHERE t3.Region IS NULL
)
-- 用LISTAGG拼接成要求的格式
SELECT LISTAGG('"' || table_name || '", ' || combo_seq || ', ' || total_missing, '; ') 
       WITHIN GROUP (ORDER BY table_name, combo_seq) AS result
FROM missing_details;

MySQL 版本（用GROUP_CONCAT替代LISTAGG） #

WITH all_combinations AS (
    SELECT Region, Customer_ID FROM Table1
    UNION
    SELECT Region, Customer_ID FROM Table2
    UNION
    SELECT Region, Customer_ID FROM Table3
),
missing_details AS (
    SELECT 
        'Table 1' AS table_name,
        LPAD(ROW_NUMBER() OVER (PARTITION BY 'Table 1' ORDER BY Region, Customer_ID), 3, '0') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 1') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table1 t1 ON ac.Region = t1.Region AND ac.Customer_ID = t1.Customer_ID
    WHERE t1.Region IS NULL
    UNION ALL
    SELECT 
        'Table 2' AS table_name,
        LPAD(ROW_NUMBER() OVER (PARTITION BY 'Table 2' ORDER BY Region, Customer_ID), 3, '0') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 2') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table2 t2 ON ac.Region = t2.Region AND ac.Customer_ID = t2.Customer_ID
    WHERE t2.Region IS NULL
    UNION ALL
    SELECT 
        'Table 3' AS table_name,
        LPAD(ROW_NUMBER() OVER (PARTITION BY 'Table 3' ORDER BY Region, Customer_ID), 3, '0') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 3') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table3 t3 ON ac.Region = t3.Region AND ac.Customer_ID = t3.Customer_ID
    WHERE t3.Region IS NULL
)
-- 用GROUP_CONCAT拼接结果
SELECT GROUP_CONCAT(
    CONCAT('"', table_name, '", ', combo_seq, ', ', total_missing)
    SEPARATOR '; '
) AS result
FROM missing_details
ORDER BY table_name, combo_seq;

SQL Server 版本（用STRING_AGG） #

WITH all_combinations AS (
    SELECT Region, Customer_ID FROM Table1
    UNION
    SELECT Region, Customer_ID FROM Table2
    UNION
    SELECT Region, Customer_ID FROM Table3
),
missing_details AS (
    SELECT 
        'Table 1' AS table_name,
        FORMAT(ROW_NUMBER() OVER (PARTITION BY 'Table 1' ORDER BY Region, Customer_ID), '000') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 1') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table1 t1 ON ac.Region = t1.Region AND ac.Customer_ID = t1.Customer_ID
    WHERE t1.Region IS NULL
    UNION ALL
    SELECT 
        'Table 2' AS table_name,
        FORMAT(ROW_NUMBER() OVER (PARTITION BY 'Table 2' ORDER BY Region, Customer_ID), '000') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 2') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table2 t2 ON ac.Region = t2.Region AND ac.Customer_ID = t2.Customer_ID
    WHERE t2.Region IS NULL
    UNION ALL
    SELECT 
        'Table 3' AS table_name,
        FORMAT(ROW_NUMBER() OVER (PARTITION BY 'Table 3' ORDER BY Region, Customer_ID), '000') AS combo_seq,
        COUNT(*) OVER (PARTITION BY 'Table 3') AS total_missing
    FROM all_combinations ac
    LEFT JOIN Table3 t3 ON ac.Region = t3.Region AND ac.Customer_ID = t3.Customer_ID
    WHERE t3.Region IS NULL
)
-- 用STRING_AGG拼接结果
SELECT STRING_AGG(
    CONCAT('"', table_name, '", ', combo_seq, ', ', total_missing),
    '; '
) WITHIN GROUP (ORDER BY table_name, combo_seq) AS result
FROM missing_details;

如果不需要列出具体缺失组合，只需要按表统计缺失数量，可以用更简洁的代码：

WITH all_combinations AS (
    SELECT Region, Customer_ID FROM Table1
    UNION
    SELECT Region, Customer_ID FROM Table2
    UNION
    SELECT Region, Customer_ID FROM Table3
),
missing_counts AS (
    SELECT 'Table 1' AS table_name, COUNT(*) AS missing_count
    FROM all_combinations ac LEFT JOIN Table1 t1 USING(Region, Customer_ID)
    WHERE t1.Region IS NULL
    UNION ALL
    SELECT 'Table 2' AS table_name, COUNT(*) AS missing_count
    FROM all_combinations ac LEFT JOIN Table2 t2 USING(Region, Customer_ID)
    WHERE t2.Region IS NULL
    UNION ALL
    SELECT 'Table 3' AS table_name, COUNT(*) AS missing_count
    FROM all_combinations ac LEFT JOIN Table3 t3 USING(Region, Customer_ID)
    WHERE t3.Region IS NULL
)
-- 替换成对应数据库的聚合函数即可
SELECT LISTAGG('"' || table_name || '", ' || missing_count, '; ') WITHIN GROUP (ORDER BY table_name) AS result
FROM missing_counts;

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