1、实现思路

首先，我们先实现查询当月每日的日期。然后将查询结果与需要统计的表进行左连接即可。

SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) AS date
FROM(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4
WHEREDATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) <= LAST_DAY(CURRENT_DATE())

比如我的表结构是这样的。

2、SQL实现

SELECT dates.date, IFNULL(SUM(cms_recharge_record.amount), 0) AS total
FROM (SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) AS dateFROM(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4WHEREDATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) <= LAST_DAY(CURRENT_DATE())
) AS dates
LEFT JOIN cms_recharge_record ON DATE(dates.date) = DATE(cms_recharge_record.create_time) AND MONTH(dates.date) = MONTH(cms_recharge_record.create_time)
GROUP BY dates.date
ORDER BY dates.date;

SQL解释：（解释来源于ChatGPT）

1、首先，使用子查询生成一个包含当月每天日期的临时表，子查询中的四个子查询（t1, t2, t3, t4）用于生成从0到9的数字序列，通过计算日期的偏移量来生成每天的日期。这样可以确保每天都有日期记录。

2、然后，使用左连接（LEFT JOIN）将临时表（dates）与cms_recharge_record表进行连接。连接条件是日期相等，并且月份相等。

3、使用IFNULL函数来处理当日没有收益记录的情况，将收益金额设置为0。

4、最后，使用GROUP BY对日期进行分组，并使用SUM函数计算每日的收益金额。

5、使用ORDER BY对日期进行排序，以确保结果按照日期的升序排列。