mysql按日期分组-无数据补0

参考：https://blog.csdn.net/JavaReact/article/details/79994555

eg:按天分组
SELECT
	adddate('2019-12-01 00:00:00', numlist.id) AS 'date'
	FROM
	(
	SELECT
	n1.i + n10.i * 10 + n100.i * 100 AS id
	FROM
	num n1
	CROSS JOIN num AS n10
	CROSS JOIN num AS n100
	) AS numlist
	WHERE
	adddate('2019-12-01 00:00:00', numlist.id) <= '2020-01-09 00:00:00'

eg:按月分组
SELECT
	adddate('2018-12-01 00:00:00', interval numlist.id month) AS 'date'
	FROM
	(
	SELECT
	n1.i + n10.i * 10 + n100.i * 100 AS id
	FROM
	num n1
	CROSS JOIN num AS n10
	CROSS JOIN num AS n100
	) AS numlist
	WHERE
	adddate('2018-12-01 00:00:00', interval numlist.id month) <= '2019-12-01 00:00:00'

eg:按小时分组
SELECT
	adddate('2018-12-23 08:00:00', interval numlist.id HOUR) AS 'date'
	FROM
	(
	SELECT
	n1.i + n10.i * 10 + n100.i * 100 AS id
	FROM
	num n1
	CROSS JOIN num AS n10
	CROSS JOIN num AS n100
	) AS numlist
	WHERE
	adddate('2018-12-23 08:00:00', interval numlist.id HOUR) <= '2018-12-23 22:00:00'

上述SQL会生成给定时间段内的每一天/月/时，剩下的与你的SQL进行左连接就好了

 

转载于:https://www.cnblogs.com/haom/p/10259667.html