mysql 查询临时变量_Mysql利用临时变量自动生成时间表统计

爱被打了一巴掌 2023-01-11 13:22 154阅读 0赞

最近需要做一组按时间统计数据库数据量的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'

![4233f75d8e3debd0a29092cc7bd1fafc.png][]

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

![2392097ba03060539a7a684db7269cda.png][]

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

![fb0c1ed362207992297ce748d26bfa3d.png][]

[4233f75d8e3debd0a29092cc7bd1fafc.png]: /images/20221119/a1fdb6d02353433cbeeeae103e912390.png
[2392097ba03060539a7a684db7269cda.png]: /images/20221119/bce169d8a7384fec90de0bf7ce8097c7.png
[fb0c1ed362207992297ce748d26bfa3d.png]: https://img-blog.csdnimg.cn/img_convert/fb0c1ed362207992297ce748d26bfa3d.png