最近需要做一组按时间统计数据库数据量的sql，考虑如何自动生成mysql时间表来解决没有数据的时间段不展示的情况 大概思路：利用mysql临时变量 @num:= 使用原表数据进行生成多条累加器数据 同时使用时间戳递减1小时 形成一组时间表，再连表查询统计结果，没有数据则补 0
实现步骤
1.生成累加器
select @num:= @num+1 from (select @num:=0 from table1 limit 100 ) n
2.时间戳递减
select date_format(@cur:= date_add(@cur, interval -1 HOUR), '%Y-%m-%d %H') as timeStr from (select @cur := CURRENT_TIMESTAMP from dual) du
**date_add函数支持按年、月、天、时、分、秒 递减，有不同的需求可以自行修改 *注意 这里的sql单独执行，只能生成一条时间戳，接下来要进行联合累加器来实现生成一组时间表
3.生成时间表
select date_format(@cur := date_add(@cur, interval -1 HOUR), '%Y-%m-%d %H') as timeStr,@num:= @num+1 from (select @num:=0 from table1 limit 100 ) n,(select @cur := CURRENT_TIMESTAMP from dual) du where @cur > '2020-09-01 00:00:00'
4.正常的按时间统计数据分组
select date_format(t.CreateTime, '%Y-%m-%d %H') timeStr, count(*) as CNT from table1 t where t.CreateTime > '2020-09-01 00:00:00' group by timeStr
5.时间表联合统计数据进行按时间表分组，自动补0
SELECT t1.timeStr, coalesce(data.CNT, 0) AS CNT FROM ( SELECT date_format(@cur := date_add(@cur, INTERVAL -1 HOUR), '%Y-%m-%d %H') AS timeStr , @num := @num + 1 FROM ( SELECT @num := 0 FROM table1 LIMIT 100 ) n, ( SELECT @cur := CURRENT_TIMESTAMP FROM dual ) du WHERE @cur > '2020-09-01 00:00:00' ) t1 LEFT JOIN ( SELECT date_format(t.CreateTime, '%Y-%m-%d %H') AS timeStr, COUNT(*) AS CNT FROM table1 t WHERE t.CreateTime > '2020-09-01 00:00:00' GROUP BY timeStr ) data ON data.timeStr = t1.timeStr ORDER BY timeStr ASC
| 
转播
