带日期范围条件的Group By与SUM SQL余额统计错误问题

错误原因 #

• 多次左连接同一张表会产生笛卡尔积：同一个company_id下不同时间区间的发票记录关联后，会生成多倍的交叉重复行，求和时balance_amount被重复累加，最终统计值远大于实际值。
• 关联的子表未添加过滤规则：原语句的过滤条件仅作用于主表，左连接的子表会把已删除、非活跃、余额为0的无效记录也纳入统计，进一步导致结果错误。
• 日期格式不规范：原语句中'2021-9-10'的写法可能在部分数据库中出现日期解析异常，建议统一为'2021-09-10'的两位月/日格式。

修正方法 #

不需要使用多表关联，直接通过单表条件聚合即可实现需求，修正后的SQL如下：

SELECT 
  company_id,
  SUM(CASE WHEN due_date >= '2021-11-10' THEN balance_amount ELSE 0 END) AS cmonth,
  SUM(CASE WHEN due_date < '2021-11-10' AND due_date >= '2021-10-10' THEN balance_amount ELSE 0 END) AS 1month,
  SUM(CASE WHEN due_date < '2021-10-10' AND due_date >= '2021-09-10' THEN balance_amount ELSE 0 END) AS 2month
FROM `invoices`
WHERE 
  status = 'ACTIVE' 
  AND balance_amount > 0 
  AND deleted_at IS NULL
GROUP BY company_id

该方案仅需扫描一次表，不仅统计结果准确，执行效率也远高于多表关联的写法。

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