如何在SQL Server中用SUM函数统计多表重复记录总数

首先得明确你想要统计的是哪种“重复记录数”，这也是你之前脚本得到错误结果的核心原因：

情况1：统计所有存在重复的记录总行数（即你最初查询返回的4+2+0+0+2=8这类结果） #

你最初的脚本逻辑其实是对的，但可能因为对子查询返回值的理解偏差导致结果不符。我们可以调整脚本，先单独计算每个表的重复总行数，再汇总，这样更清晰：

SELECT SUM(total_duplicate_rows) AS GrandTotal
FROM (
    -- 订阅者表：所有重复记录的总行数
    SELECT SUM(dup_count) AS total_duplicate_rows
    FROM (
        SELECT COUNT(*) AS dup_count
        FROM tbl_Subscribers
        WHERE user_id='1' AND category_id='17'
        GROUP BY EmailAddress
        HAVING COUNT(*) > 1
    ) AS sub_dups
    UNION ALL
    -- 发件人表：所有重复记录的总行数
    SELECT SUM(dup_count) AS total_duplicate_rows
    FROM (
        SELECT COUNT(*) AS dup_count
        FROM tbl_From_master
        WHERE user_id='1'
        GROUP BY EmailAddress
        HAVING COUNT(*) > 1
    ) AS from_dups
    UNION ALL
    -- 分类表：所有重复记录的总行数
    SELECT SUM(dup_count) AS total_duplicate_rows
    FROM (
        SELECT COUNT(*) AS dup_count
        FROM tbl_Categories
        WHERE user_id='1'
        GROUP BY CategoryName
        HAVING COUNT(*) > 1
    ) AS cat_dups
    UNION ALL
    -- 模板分类表：所有重复记录的总行数
    SELECT SUM(dup_count) AS total_duplicate_rows
    FROM (
        SELECT COUNT(*) AS dup_count
        FROM tbl_Template_Categories
        WHERE user_id='1'
        GROUP BY CategoryName
        HAVING COUNT(*) > 1
    ) AS tcat_dups
    UNION ALL
    -- 模板表：所有重复记录的总行数
    SELECT SUM(dup_count) AS total_duplicate_rows
    FROM (
        SELECT COUNT(*) AS dup_count
        FROM tbl_Template_master
        WHERE user_id='1'
        GROUP BY TemplateName
        HAVING COUNT(*) > 1
    ) AS tpl_dups
) AS all_table_totals

这个脚本会先计算每个表中每个重复分组的记录数，求和得到该表的重复总行数，最后把所有表的结果加起来，和你最初查询返回的受影响行数总和一致。

情况2：统计真正的重复项数量（即每组中超出1条的部分，比如一个Email出现3次，重复数是2） #

如果你想要的是“需要清理的重复记录数”（也就是每组去掉第一条后的数量），那要计算COUNT(*) - 1的总和，脚本如下：

SELECT SUM(duplicate_count) AS GrandTotal
FROM (
    -- 订阅者表：重复项数量
    SELECT (COUNT(*) - 1) AS duplicate_count
    FROM tbl_Subscribers
    WHERE user_id='1' AND category_id='17'
    GROUP BY EmailAddress
    HAVING COUNT(*) > 1
    UNION ALL
    -- 发件人表：重复项数量
    SELECT (COUNT(*) - 1) AS duplicate_count
    FROM tbl_From_master
    WHERE user_id='1'
    GROUP BY EmailAddress
    HAVING COUNT(*) > 1
    UNION ALL
    -- 分类表：重复项数量
    SELECT (COUNT(*) - 1) AS duplicate_count
    FROM tbl_Categories
    WHERE user_id='1'
    GROUP BY CategoryName
    HAVING COUNT(*) > 1
    UNION ALL
    -- 模板分类表：重复项数量
    SELECT (COUNT(*) - 1) AS duplicate_count
    FROM tbl_Template_Categories
    WHERE user_id='1'
    GROUP BY CategoryName
    HAVING COUNT(*) > 1
    UNION ALL
    -- 模板表：重复项数量
    SELECT (COUNT(*) - 1) AS duplicate_count
    FROM tbl_Template_master
    WHERE user_id='1'
    GROUP BY TemplateName
    HAVING COUNT(*) > 1
) AS all_duplicate_counts

为什么你之前得到错误结果？ #

你之前的脚本返回7，大概率是因为实际数据的重复分组情况和你预期的受影响行数不符。比如某个表的重复分组里有一个组是3条记录（COUNT(*)=3），另一个组是2条（COUNT(*)=2），那这两个组的和是5，加上其他表的2和2，总和就是9，和你预期的8不符。用上面第一种情况的脚本可以明确看到每个表的具体数值，方便排查数据问题。

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