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

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

生成最近24小时整点

SELECT
    -- 每向下推1行, @i比上次减去1
		b.*, i.*,
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL ( -( @i := @i - 1 ) ) HOUR ), '%Y-%m-%d %H:00' ) AS 'time' 
FROM
	   -- 目的是生成12行数据
		( SELECT
			a 
		  FROM
			( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS a
			JOIN ( 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分钟

SELECT
	DATE_FORMAT( DATE_SUB( ( SELECT DATE_FORMAT( NOW(), '%Y-%m-%d %H:00' )), INTERVAL ( -( @i := @i - 30 ) ) MINUTE ), '%Y-%m-%d %H:%i' ) AS 'time' 
FROM
	(
	SELECT
		a 
	FROM
		( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS a
		JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' ) AS b ON 1 
	) AS b,
	(SELECT @i := 30 ) AS i

生成最近30天

同理，往前推30天

SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL ( -( @i := @i - 1 ) ) DAY ), '%Y-%m-%d' ) AS 'time' 
FROM
	(
		SELECT
			a 
		FROM
			( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' ) AS a
			JOIN 
			( 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 

其它写法：

SELECT
    DATE_FORMAT( DATE_SUB( NOW( ), INTERVAL xc DAY ), '%Y-%m-%d' ) AS date 
FROM
    (
        SELECT
            @xi := @xi + 1 AS xc 
        FROM
            ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) xc1,
            ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc2,
            ( SELECT @xi :=- 1 ) xc0 
    ) t

生成最近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`

使用示例

SELECT
    t1.t_date AS sDate,
    IFNULL(t2.report_num,0) AS disposeNum,
    IFNULL(t3.dispose_num,0) AS reportNum
FROM
    (
        SELECT
                DATE_FORMAT( DATE_SUB( NOW(), INTERVAL ( -( @i := @i - 1 ) ) DAY ), '%Y-%m-%d' ) AS t_date
        FROM
            (
                SELECT
                    a
                FROM
                    ( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' ) AS a
                    JOIN
                    ( 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 t_date
    ) t1
    LEFT JOIN(
        SELECT
            DATE( tk.create_time ) s_date,
            COUNT(*) report_num
        FROM
            rm_repair_flow_task tk
        WHERE
            tk.community_id = #{projectId}
            AND tk.status_cd = 0
            AND date( tk.create_time ) >= DATE(DATE_SUB(NOW(), INTERVAL 29 DAY))
            AND date( tk.create_time ) <= DATE(NOW())
        group by
            s_date
    ) t2 ON t1.t_date = t2.s_date
    LEFT JOIN(
        SELECT
            DATE(t.d_time) s_date,
            COUNT(t.repair_task_id) dispose_num
        FROM
            (
                SELECT
                    repair_task_id,
                    MAX(td.create_time) d_time
                FROM
                    rm_repair_dispose_detail td
                    LEFT JOIN rm_repair_flow_task tk on td.repair_task_id = tk.id
                WHERE
                    tk.community_id = #{projectId}
                    AND td.status_cd = 0
                    AND date( td.create_time ) >= DATE(DATE_SUB(NOW(), INTERVAL 29 DAY))
                    AND date( td.create_time ) <= DATE(NOW())
                GROUP BY
                    td.repair_task_id
            ) t
        GROUP BY
            s_date
    )t3 ON t1.t_date = t3.s_date