MySQL根据所传时间段进行日期分组统计数据，没有数据返回0

一、根据每日分组

先查出时间段内的每一天

SELECT
	date_add( date_sub( #{startDate}, INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ) days 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DATEDIFF( #{endDate}, date_sub( #{startDate}, INTERVAL 1 DAY ) ) 
ORDER BY
	help_topic_id

再使用外连接查询完成自己的业务

SELECT
	days.days date,
	IFNULL( SUM( b.total_price ), 0 ) num 
FROM
	(
SELECT
	date_add( date_sub( #{startDate}, INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ) days 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DATEDIFF( #{endDate}, date_sub( #{startDate}, INTERVAL 1 DAY ) ) 
ORDER BY
	help_topic_id 
	) days
	LEFT JOIN ds_order b ON TO_DAYS( b.create_time) = TO_DAYS( days.days ) 
GROUP BY
	days.days

注意：
如果查询SQL时提示“1142 - SELECT command denied to user ‘asd’@‘ip地址’ for table ‘help_topic’”，表明没有权限访问help_topic表。可以创建一个help_topic表，只需要有help_topic_id列自增，并且填充一些数据：1,2,3,4,5,6,7…，将查询SQL语句中的mysql.去除，即可满足该功能。

二、根据月份分组

获取所传时间段内每个月份

SELECT
	DATE_FORMAT( m1, '%Y-%m' ) m1 
FROM
	(
SELECT
	( '2023-11-01' - INTERVAL DAYOFMONTH( '2023-11-01' ) - 1 DAY ) + INTERVAL m MONTH AS m1 
FROM
	(
SELECT
	@rownum := @rownum + 1 AS m 
FROM
	( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t1,
	( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t2,
	( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t3,
	( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t4,
	( SELECT @rownum :=- 1 ) t0 
	) d1 
	) d2 
WHERE
	m1 <= '2024-01-01' 
ORDER BY
	m1

再使用外连接查询完成自己的业务