MySQL根据日期统计时没有数据的日期也需要返回0

我们经常会碰需要根据日期统计数据的问题，正常情况 GROUP BY 日期就可以得到我们想要的数据，但是有些时候某些日期没有数据可是我们又想它能返回一个0，这个时候 GROUP BY 就不行了

先看 GROUP BY 查询

SELECT count_date,
	   SUM(IFNULL(total_amount, 0)) AS total_amount,
       SUM(IFNULL(commission_amount, 0)) AS commission_amount
FROM qy_commission_count
WHERE agent_id = 4
AND count_date BETWEEN '2024-09-01' AND '2024-09-11'
GROUP BY count_date;

现在我们希望在没有数据的日期也返回该日期和对应的 0 值，这里可以通过创建一个日期序列来实现

WITH RECURSIVE date_range AS (
    SELECT '2024-09-01' AS count_date
    UNION ALL
    SELECT DATE_ADD(count_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE count_date < '2024-09-11'
)
SELECT dr.count_date,
       SUM(IFNULL(qcc.total_amount, 0)) AS total_amount,
       SUM(IFNULL(qcc.commission_amount, 0)) AS commission_amount
FROM date_range dr
LEFT JOIN qy_commission_count qcc 
       ON dr.count_date = qcc.count_date
       AND qcc.agent_id = 4
GROUP BY dr.count_date
ORDER BY dr.count_date;

从上面的结果可以看出除了有数据的日期，没有数据的日期也会返回一个0.00，这里要注意 WITH RECURSIVE 是 MySQL8.0 才有的