PostgreSQL中计算两个Schema下同表的去重ID计数差值

算力阿峰

2026-8-31

跨Schema同名表去重ID差异统计SQL

你原有写法可以直接简化优化，不需要嵌套子查询做去重再计数，直接用count(DISTINCT id)就能达到相同统计效果，再通过交叉连接两个统计结果集，就能输出你需要的结构化对比结果。
注意你给出的预期输出示例存在字段笔误，第二个计数字段应为count_old而非重复的count_new，最终可直接运行的SQL如下：

SELECT
    'compound' AS table_name,
    t_new.count_new,
    t_old.count_old,
    t_new.count_new - t_old.count_old AS diff
FROM
    (SELECT COUNT(DISTINCT id) AS count_new FROM schema2.compound) AS t_new,
    (SELECT COUNT(DISTINCT id) AS count_old FROM schema1.compound) AS t_old;

如果后续需要批量对比多个跨Schema同名表，可以用UNION ALL拼接多个查询块实现批量统计，示例写法：

SELECT
    'compound' AS table_name,
    (SELECT COUNT(DISTINCT id) FROM schema2.compound) AS count_new,
    (SELECT COUNT(DISTINCT id) FROM schema1.compound) AS count_old,
    (SELECT COUNT(DISTINCT id) FROM schema2.compound) - (SELECT COUNT(DISTINCT id) FROM schema1.compound) AS diff
UNION ALL
SELECT
    'table2' AS table_name,
    (SELECT COUNT(DISTINCT id) FROM schema2.table2) AS count_new,
    (SELECT COUNT(DISTINCT id) FROM schema1.table2) AS count_old,
    (SELECT COUNT(DISTINCT id) FROM schema2.table2) - (SELECT COUNT(DISTINCT id) FROM schema1.table2) AS diff;