MySQL三表关联查询优先取tableB值计算金额的实现方案

问题根因 #

你当前写法的问题是直接同时left join tableB和tableC，当同一个a_id在两张表都有匹配数据时，会产生笛卡尔积，也就是数据行数会被相乘放大。比如id=2在tableB有2条记录、tableC有2条记录，join后会生成2*2=4条记录，直接sum就会导致金额被重复累加，才得到错误的120的结果。

解决方案 #

正确的做法是先分别对tableB、tableC按a_id分组统计好非DEPOSIT类型的总金额，再和tableA关联判断取值，避免笛卡尔积问题，参考SQL如下：

SELECT 
  A.id,
  IF(IFNULL(B.sum_amount, 0) > 0, B.sum_amount, IFNULL(C.sum_amount, 0)) AS total
FROM tableA A
LEFT JOIN (
  SELECT 
    a_id, 
    SUM(amount) AS sum_amount 
  FROM tableB 
  WHERE code != 'DEPOSIT'
  GROUP BY a_id
) B ON A.id = B.a_id
LEFT JOIN (
  SELECT 
    a_id, 
    SUM(amount) AS sum_amount 
  FROM tableC 
  WHERE code != 'DEPOSIT'
  GROUP BY a_id
) C ON A.id = C.a_id
ORDER BY A.id;

验证结果 #

执行上述SQL后输出的结果和要求完全匹配：

• id=1：tableB无对应数据，取tableC的总和60
• id=2：tableB总和为10+20=30，优先取30
• id=3：tableB总和为70，优先取70

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