关联Table1与Table2查询cid=23的Total、Advance、Pending字段失败求助

首先，我先理清楚你的需求，确保没理解错：

• Total：Table1中cid=23且w_id IN (1,2)的total字段之和（500+300=800）
• Advance：Table1对应条件的advance之和（100+100=200）加上Table2中w_id IN (1,2)的advance之和（100+100+100+150=450），最终总和650
• Pending：Total减去Advance（800-650=150）

你的原SQL没成功的核心问题是：用LEFT JOIN直接关联Table1和Table2时，Table2中同一个w_id的多条记录会和Table1的对应记录重复匹配，导致Table1的total和advance被重复累加，结果自然不对。

正确的解法：先分别汇总，再关联 #

我们可以先分别计算Table1和Table2的目标汇总值，再把它们组合起来，彻底避免笛卡尔积导致的重复计算：

SELECT
    t1.cid,
    t1.total_sum AS Total,
    (t1.advance_sum + t2.advance_sum) AS Advance,
    (t1.total_sum - (t1.advance_sum + t2.advance_sum)) AS Pending
FROM
    (
        SELECT
            cid,
            SUM(total) AS total_sum,
            SUM(advance) AS advance_sum
        FROM table1
        WHERE cid = 23 AND w_id IN (1,2)
        GROUP BY cid
    ) AS t1
CROSS JOIN
    (
        SELECT
            SUM(advance) AS advance_sum
        FROM table2
        WHERE w_id IN (1,2)
    ) AS t2;

另一种简化写法：用子查询直接计算Table2的总和 #

如果只需要cid=23这一条结果，也可以在SELECT语句里直接嵌套子查询获取Table2的汇总值，写法更简洁：

SELECT
    cid,
    SUM(total) AS Total,
    (SUM(advance) + (SELECT SUM(advance) FROM table2 WHERE w_id IN (1,2))) AS Advance,
    (SUM(total) - (SUM(advance) + (SELECT SUM(advance) FROM table2 WHERE w_id IN (1,2)))) AS Pending
FROM table1
WHERE cid = 23 AND w_id IN (1,2)
GROUP BY cid;

这两种写法都能准确得到你想要的结果，第一种逻辑更清晰，适合后续需要扩展多个条件的场景；第二种更精简，适配单一条件的需求。

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