Mysql查询最近30天数据 - 自动补全日期

mysql中统计数据，按照日期汇总，如果某一天没有数据，会没有对应的时间，可以使用一下方法解决

-- 查询时间，并统计数据
select all_day,count(td.id) from 
(
	-- 生成日期 
	SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL day_p DAY), '%Y-%m-%d') as all_day FROM ( 
	  -- 生成一个序号 
		SELECT @day_p:=@day_p+1 as day_p from 
		-- 数字 5 * 6 = 30 改成自己需要的天数即可 【此处为连接查询 没有链接条件，结果为表1行数 * 表2行数】
		(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) ac1, 
		(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac2,
		-- 声明从0开始
		(SELECT @day_p:=1) a
	) ad 
) months LEFT JOIN 表名  td 
-- 连接上要查询的表，链接条件为生成的日期 等于 表中的日期 
on months.all_day =  DATE_FORMAT( td.create_time ,'%Y-%m-%d' ) GROUP BY all_day

统计今年开始的小时。到现在

SELECT DATE_FORMAT( 
		DATE_SUB( 
			DATE_SUB( "2021-01-01 00:00:00", INTERVAL -date_day day  ),  
			INTERVAL -date_hour HOUR
		), 
		'%Y-%m-%d %H') as all_day 
FROM 
(
	SELECT 
		IF(@date_h>=23,@date_d:=@date_d+1, @date_d:=@date_d) as date_day,
		IF( @date_h<23,@date_h:=@date_h+1 ,@date_h:=0 ) as date_hour 
	from (
			(SELECT 1 union SELECT 2 UNION SELECT 3 UNION SELECT 4 ) as a1,
			(SELECT 1 union SELECT 2 UNION SELECT 3 UNION SELECT 4 ) as a2,
			(SELECT 1 union SELECT 2 UNION SELECT 3 UNION SELECT 4 ) as a3,
			(SELECT 1 union SELECT 2 UNION SELECT 3 UNION SELECT 4 ) as a4,
			(SELECT 1 union SELECT 2 UNION SELECT 3 UNION SELECT 4 ) as a5,
			(SELECT 1 union SELECT 2 UNION SELECT 3 UNION SELECT 4 ) as a6,
			(SELECT 1 union SELECT 2 UNION SELECT 3 UNION SELECT 4 ) as a7,
			(SELECT @date_d:=0) as a,
			(SELECT @date_h:=0) as b
		) 
) as aaaa HAVING all_day < NOW()  ORDER BY all_day desc