求助：MySQL中Sum()函数返回值翻倍，多表联查求和异常

嘿，我太懂这个坑了！当你直接把tbl_receipt和tbl_invoice左连接到tbl_member时，会触发笛卡尔积效应——拿你的数据举例，member_id=21有2条收款记录和2条发票记录，连接后会生成2×2=4条组合行。这时候SUM()函数会把每条收款金额重复计算两次，每条发票金额也重复计算两次，结果自然就变成预期的两倍了。

正确的解决思路 #

核心是先聚合，再连接：先分别对收款表和发票表按member_id分组求和，得到每个会员的独立汇总数据，再把这些汇总结果和会员表关联，这样就不会产生重复行的问题。

完整SQL代码 #

SELECT 
    m.id AS member_id,
    m.name AS member_name,
    COALESCE(r.receipt_sum, 0) AS receipt_sum,
    COALESCE(i.bill_sum, 0) AS bill_sum
FROM tbl_member m
LEFT JOIN (
    -- 先对收款表按会员分组求和
    SELECT member_id, SUM(receipt_amount) AS receipt_sum
    FROM tbl_receipt
    GROUP BY member_id
) r ON m.id = r.member_id
LEFT JOIN (
    -- 再对发票表按会员分组求和
    SELECT member_id, SUM(bill_value) AS bill_sum
    FROM tbl_invoice
    GROUP BY member_id
) i ON m.id = i.member_id
-- 如果只需要特定会员的数据，加上这个条件
WHERE m.id = 21;

关键细节说明 #

• COALESCE()函数：用来处理某个会员没有收款记录或发票记录的情况，把默认的NULL替换成0，更符合业务展示需求。
• 子查询聚合：每个子查询只会返回每个会员一行汇总数据，连接后不会产生笛卡尔积，SUM()的结果就完全准确了。

如果你用的是PHP的数据库适配器（比如TableGateway），只需要把这段SQL作为查询语句传入适配器执行即可，比如：

$sql = "SELECT 
    m.id AS member_id,
    m.name AS member_name,
    COALESCE(r.receipt_sum, 0) AS receipt_sum,
    COALESCE(i.bill_sum, 0) AS bill_sum
FROM tbl_member m
LEFT JOIN (
    SELECT member_id, SUM(receipt_amount) AS receipt_sum
    FROM tbl_receipt
    GROUP BY member_id
) r ON m.id = r.member_id
LEFT JOIN (
    SELECT member_id, SUM(bill_value) AS bill_sum
    FROM tbl_invoice
    GROUP BY member_id
) i ON m.id = i.member_id
WHERE m.id = 21;";

$result = $adapter->query($sql, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$row = $result->current();

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