MySQL多表Inner Join查询重复问题：统计销售与存款总额

当直接用INNER JOIN关联销售表（contometria）和存款表（deposito）做总额统计时，会因为表间的一对多关系产生笛卡尔积，导致统计结果被重复计算。要解决这个问题，核心思路是先分别对单表做聚合统计，再关联统计后的结果，避免原始记录的重复匹配。

错误查询示例（导致重复计算） #

假设你之前的查询类似这样：

SELECT 
    SUM(c.valor) AS total_contometria,
    SUM(d.valor) AS total_deposito,
    SUM(c.valor) - SUM(d.valor) AS diferenca
FROM contometria c
INNER JOIN deposito d ON c.cliente_id = d.cliente_id;

如果某客户有2条销售记录、3条存款记录，关联后会生成6条重复记录，最终SUM的结果会被放大，完全偏离真实值。

修正方案一：全局总额统计（所有客户合计） #

如果只需要统计所有销售和存款的总金额，直接用子查询分别计算两个表的总额是最简洁的方式：

SELECT
    COALESCE((SELECT SUM(valor) FROM contometria), 0) AS total_contometria,
    COALESCE((SELECT SUM(valor) FROM deposito), 0) AS total_deposito,
    COALESCE((SELECT SUM(valor) FROM contometria), 0) - COALESCE((SELECT SUM(valor) FROM deposito), 0) AS diferenca;

• COALESCE用来处理某表无数据时的NULL值，确保结果始终为数字0而非NULL。

修正方案二：按客户维度统计 #

如果需要按每个客户分别统计销售总额、存款总额及差值，先对两个表按客户分组聚合，再关联结果：

-- 统计有存款记录的客户，包括无销售记录的
SELECT
    COALESCE(c.total_contometria, 0) AS total_contometria,
    d.total_deposito AS total_deposito,
    COALESCE(c.total_contometria, 0) - d.total_deposito AS diferenca,
    d.cliente_id
FROM
    (SELECT cliente_id, SUM(valor) AS total_contometria FROM contometria GROUP BY cliente_id) c
RIGHT JOIN
    (SELECT cliente_id, SUM(valor) AS total_deposito FROM deposito GROUP BY cliente_id) d
ON c.cliente_id = d.cliente_id

UNION ALL

-- 补充只有销售记录、无存款记录的客户
SELECT
    c.total_contometria AS total_contometria,
    0 AS total_deposito,
    c.total_contometria - 0 AS diferenca,
    c.cliente_id
FROM
    (SELECT cliente_id, SUM(valor) AS total_contometria FROM contometria GROUP BY cliente_id) c
LEFT JOIN
    (SELECT cliente_id FROM deposito GROUP BY cliente_id) d
ON c.cliente_id = d.cliente_id
WHERE d.cliente_id IS NULL;

这个方案通过RIGHT JOIN+UNION ALL模拟MySQL不支持的FULL OUTER JOIN，确保所有有业务记录的客户都被统计到。

核心原理 #

先对单表做GROUP BY聚合，每个客户只生成一条统计记录，再关联时就不会产生笛卡尔积，从根源上避免了重复计算的问题。

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