INNER JOIN关联后SUM求和重复值问题及解决方法咨询

嘿，这个问题我之前做月度统计报表的时候也踩过一模一样的坑！核心原因是你通过INNER JOIN关联transfers和Fdata后，settle表的单条记录被多次匹配（比如一个settle对应多条transfers，或者一条transfers对应多条Fdata），导致SUM(total_price)把同一个settle的价格重复累加了。

下面给你几种可行的解决思路：

方法1：先对关联表去重，再关联settle表 #

先通过子查询把transfers和Fdata关联后的settleId去重，确保每个settleId只出现一次，再和settle表关联，这样就不会产生重复记录：

SELECT 
    CONCAT(YEAR(a.datetime), '-', MONTH(a.datetime)) AS YearMonth,
    COUNT(a.id) AS TOTAL,
    SUM(a.total_price) AS TotalPrice
FROM settle AS a WITH (NOLOCK)
INNER JOIN (
    -- 子查询获取唯一的settleId，避免重复关联
    SELECT DISTINCT b.settleId 
    FROM transfers b WITH (NOLOCK)
    INNER JOIN Fdata AS c WITH (NOLOCK) ON c.id = b.data
) AS b_unique ON b_unique.settleId = a.id
GROUP BY CONCAT(YEAR(a.datetime), '-', MONTH(a.datetime))

方法2：用EXISTS替代INNER JOIN（推荐） #

如果你只需要确认settle记录存在对应的关联数据，不需要从transfers或Fdata获取其他字段，用EXISTS是最简洁的方式——它只会检查匹配是否存在，不会返回重复的settle记录：

SELECT 
    CONCAT(YEAR(a.datetime), '-', MONTH(a.datetime)) AS YearMonth,
    COUNT(a.id) AS TOTAL,
    SUM(a.total_price) AS TotalPrice
FROM settle AS a WITH (NOLOCK)
WHERE EXISTS (
    SELECT 1
    FROM transfers b WITH (NOLOCK)
    INNER JOIN Fdata AS c WITH (NOLOCK) ON c.id = b.data
    WHERE b.settleId = a.id
)
GROUP BY CONCAT(YEAR(a.datetime), '-', MONTH(a.datetime))

这里COUNT(a.id)也不需要加DISTINCT了，因为每条settle记录只会被统计一次。

方法3：对关联表按settleId聚合（如需保留关联表字段） #

如果后续需要从transfers或Fdata获取聚合值（比如每个settle对应的Fdata数量），可以先按settleId分组聚合，再关联：

SELECT 
    CONCAT(YEAR(a.datetime), '-', MONTH(a.datetime)) AS YearMonth,
    COUNT(a.id) AS TOTAL,
    SUM(a.total_price) AS TotalPrice
    -- 可以加关联表的聚合字段，比如：SUM(b_agg.data_count) AS TotalDataCount
FROM settle AS a WITH (NOLOCK)
INNER JOIN (
    SELECT 
        b.settleId,
        COUNT(c.id) AS data_count -- 示例：统计每个settle对应的Fdata数量
    FROM transfers b WITH (NOLOCK)
    INNER JOIN Fdata AS c WITH (NOLOCK) ON c.id = b.data
    GROUP BY b.settleId -- 按settleId分组，确保每个settleId仅一条记录
) AS b_agg ON b_agg.settleId = a.id
GROUP BY CONCAT(YEAR(a.datetime), '-', MONTH(a.datetime))

核心思路总结 #

不管用哪种方法，核心都是避免settle表的单条记录被多次匹配——要么提前对关联表做去重/聚合，要么用EXISTS这种不产生重复结果的关联逻辑，这样SUM(total_price)就只会计算每个settle记录的价格一次，不会出现重复累加的问题。

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