MySQL 5.7双表近30天日统计查询：无数据返回0

原查询存在的问题 #

1. GROUP BY 规则违反：MySQL 5.7 默认启用严格模式，要求GROUP BY必须包含所有SELECT中的非聚合字段。原查询中SELECT的TransDate来自payments.pay_date，但GROUP BY使用的是invoices.invoice_date，二者不匹配导致报错。
2. LEFT JOIN 失效：WHERE子句中加入了payments.active='1'等针对payments表的过滤条件，会把LEFT JOIN返回的payments为NULL的行全部过滤，最终退化为INNER JOIN，无法获取仅存在发票数据的日期。
3. 数据统计不准确：通过invoice_no关联两张表时，若一个发票对应多笔付款，会导致发票金额被重复累加，计算结果失真。
4. 无数据日期缺失：未生成完整的近30天日期序列，导致没有交易的日期不会出现在结果中，无法满足“无数据返回0”的需求。

修正后的查询方案 #

SELECT 
    d.trans_date AS TransDate,
    COALESCE(p.pmt_total, 0) AS PmtTOTAL,
    COALESCE(i.inv_total, 0) AS InvTOTAL
FROM (
    -- 生成近30天的完整日期序列
    SELECT DATE_SUB(CURDATE(), INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) AS trans_date
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2) AS c
    WHERE DATE_SUB(CURDATE(), INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
) d
LEFT JOIN (
    -- 统计每日收款金额总和（过滤无效支付类型）
    SELECT 
        DATE(STR_TO_DATE(pay_date, '%d-%m-%Y')) AS trans_date,
        ROUND(SUM(pay_amount), 2) AS pmt_total
    FROM payments
    WHERE active = '1'
      AND pay_type NOT IN ('Desconto', 'AJUSTE', 'ESTORNO')
      AND DATE(STR_TO_DATE(pay_date, '%d-%m-%Y')) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
    GROUP BY DATE(STR_TO_DATE(pay_date, '%d-%m-%Y'))
) p ON d.trans_date = p.trans_date
LEFT JOIN (
    -- 统计每日发票金额总和
    SELECT 
        DATE(invoice_date) AS trans_date,
        ROUND(SUM(invoice_total), 2) AS inv_total
    FROM invoices
    WHERE active = '1'
      AND DATE(invoice_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
    GROUP BY DATE(invoice_date)
) i ON d.trans_date = i.trans_date
ORDER BY d.trans_date ASC;

方案说明 #

1. 生成完整日期序列：通过数字表交叉连接生成近30天的所有日期，确保每一天都出现在结果中。
2. 独立统计两张表数据：分别对invoices和payments按日期聚合计算总和，避免关联时的重复统计问题。
3. 处理空值为0：使用COALESCE函数将无数据的合计值转为0，满足需求。
4. 符合GROUP BY规则：每个子查询的GROUP BY字段与SELECT的非聚合字段一致，适配MySQL 5.7的严格模式。

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