mysql生成最近24小时整点最近30天最近12个月时间临时表

本文主要是介绍mysql生成最近24小时整点最近30天最近12个月时间临时表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

- 生成最近24小时整点
- 生成最近30天
- 生成最近12个月

在统计的时候需要按时间来展示，但是数据的时间不一定是连续的，那就需要在代码里面生成连续的时间，然后按时间匹配到对应的数据，这样比较麻烦，可以在sql中使用连接查询与会话变量生成连续的时间，做成1个临时表，然后再来连接上数据表。

生成最近24小时整点

SELECT-- 每向下推1行, @i比上次减去1b.*, i.*,DATE_FORMAT( DATE_SUB( NOW(), INTERVAL ( -( @i := @i - 1 ) ) HOUR ), '%Y-%m-%d %H:00' ) AS 'time' 
FROM-- 目的是生成12行数据( SELECTa FROM( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS aJOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 ) AS b,-- 先给1个默认的值( SELECT @i := 1 ) AS i -- 每次跟主表连接1次, 都会动态计算列select中的值（就跟2张普通的表按条件连接起来,然后取表中的字段一样,只不过这里取的是@i,而@i属于会话变量而已）
-- ORDER BY time

在这里插入图片描述

生成最近30天

同理，往前推30天

SELECTDATE_FORMAT( DATE_SUB( NOW(), INTERVAL ( -( @i := @i - 1 ) ) DAY ), '%Y-%m-%d' ) AS 'time' 
FROM(SELECTa FROM( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' ) AS aJOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 ) AS b,( SELECT @i := 1 ) AS i 
ORDER BY time

在这里插入图片描述

生成最近12个月

SELECT DATE_FORMAT(CURDATE(), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%m') AS `month`

在这里插入图片描述

使用示例

SELECTt1.t_date AS sDate,IFNULL(t2.report_num,0) AS disposeNum,IFNULL(t3.dispose_num,0) AS reportNum
FROM(SELECTDATE_FORMAT( DATE_SUB( NOW(), INTERVAL ( -( @i := @i - 1 ) ) DAY ), '%Y-%m-%d' ) AS t_dateFROM(SELECTaFROM( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' ) AS aJOIN( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1) AS b,( SELECT @i := 1 ) AS iORDER BY t_date) t1LEFT JOIN(SELECTDATE( tk.create_time ) s_date,COUNT(*) report_numFROMrm_repair_flow_task tkWHEREtk.community_id = #{projectId}AND tk.status_cd = 0AND date( tk.create_time ) >= DATE(DATE_SUB(NOW(), INTERVAL 29 DAY))AND date( tk.create_time ) &lt;= DATE(NOW())group bys_date) t2 ON t1.t_date = t2.s_dateLEFT JOIN(SELECTDATE(t.d_time) s_date,COUNT(t.repair_task_id) dispose_numFROM(SELECTrepair_task_id,MAX(td.create_time) d_timeFROMrm_repair_dispose_detail tdLEFT JOIN rm_repair_flow_task tk on td.repair_task_id = tk.idWHEREtk.community_id = #{projectId}AND td.status_cd = 0AND date( td.create_time ) >= DATE(DATE_SUB(NOW(), INTERVAL 29 DAY))AND date( td.create_time ) &lt;= DATE(NOW())GROUP BYtd.repair_task_id) tGROUP BYs_date)t3 ON t1.t_date = t3.s_date