关联mq_billing与mq_paymentlist表后按计数求和结果错误如何解决

算力阿峰

2026-10-6

问题原因

你当前的SQL是直接关联两张表后再做聚合计算，当同一个Entity_Name在mq_billing表有M条记录、在mq_paymentlist表有N条记录时，关联后会生成M*N条中间数据，导致求和时数值被重复乘以关联的记录条数，就是你遇到的发票金额计算错误的原因。

修正方案

先分别对两张表做独立聚合，得到各自的统计结果后再按Entity_Name关联，修正后的SQL如下：

SELECT 
  b.Entity_Name,
  b.`Total Customer`,
  b.`Invoice Amount`,
  COALESCE(p.`Online Payment`, 0) AS `Online Payment`,
  COALESCE(p.`Cash Payment`, 0) AS `Cash Payment`,
  COALESCE(p.`Total Payment`, 0) AS `Total Payment`
FROM (
  -- 单独聚合账单表，计算客户数、发票总金额
  SELECT 
    Entity_Name,
    COUNT(Customer_Nbr) AS `Total Customer`,
    SUM(Invoice_Amount) AS `Invoice Amount`
  FROM mq_billing
  GROUP BY Entity_Name
) b
LEFT JOIN (
  -- 单独聚合支付表，计算各支付方式金额、总支付金额
  SELECT 
    Entity_Name,
    SUM(CASE WHEN Payment_Mode = 'Online Payment' THEN Amount ELSE 0 END) AS `Online Payment`,
    SUM(CASE WHEN Payment_Mode = 'Cash' THEN Amount ELSE 0 END) AS `Cash Payment`,
    SUM(Amount) AS `Total Payment`
  FROM mq_paymentlist
  GROUP BY Entity_Name
) p ON b.Entity_Name = p.Entity_Name
ORDER BY b.Entity_Name;