mysql自动补充缺失日期

场景：
在坐数据展示时，通过时间group by只显示数据库有记录的时间，没有记录不显示、前段坐图表还需这些数据，缺失的日期自动补0
解决办法
1、查出数据后代码补全数据
2、添加一张日历表、初始化几年的数据，关联进行查询
3、纯sql实现：

####测试数据
			SELECT
				DATE( u.createdDate ) AS create_time,
				count( u.id ) AS total
			FROM
				hr_order u
				where 
				u.createdDate> '2021-10-01' 
			GROUP BY
				DATE( u.createdDate );

;运行结果：

###test sql
			SET @i :=- 1;
			SELECT
				x.`time`,
				IFNULL( d.total, 0 ) AS total
			FROM
				(
				SELECT
					date_format( DATE_SUB( NOW( ), INTERVAL ( @i := @i + 1 ) DAY ), '%Y-%m-%d' ) AS `time` 
				FROM
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 
						(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2
				WHERE
					@i <= 30 
				) x
				LEFT JOIN 
				(
				SELECT
					DATE( u.createdDate ) AS createdDate,
					count( u.id ) AS total
				FROM
					hr_order u 
					where 
				u.createdDate> '2021-10-01' 
				GROUP BY
					DATE( u.createdDate ) 
				) d ON TO_DAYS( x.`time` ) = TO_DAYS( DATE( d.createdDate ) ) 
			ORDER BY
				x.`time`

运行结果：