快捷導(dǎo)航

使用MySQL生成最近24小時(shí)整點(diǎn)時(shí)間臨時(shí)表

更新時(shí)間：2024年01月23日 11:26:00 作者：ps酷教程

MySQL臨時(shí)表是一種只存在于當(dāng)前數(shù)據(jù)庫連接或會(huì)話期間的表,它們可以被用來存儲(chǔ)臨時(shí)數(shù)據(jù),這些數(shù)據(jù)可以在查詢中被使用,但是它們不會(huì)在數(shù)據(jù)庫中永久存儲(chǔ),這篇文章主要給大家介紹了關(guān)于如何使用MySQL生成最近24小時(shí)整點(diǎn)時(shí)間臨時(shí)表的相關(guān)資料,需要的朋友可以參考下

生成最近24小時(shí)整點(diǎn)

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行數(shù)據(jù)
		( 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個(gè)默認(rèn)的值
		( SELECT @i := 1 ) AS i -- 每次跟主表連接1次, 都會(huì)動(dòng)態(tài)計(jì)算列select中的值（就跟2張普通的表按條件連接起來,然后取表中的字段一樣,只不過這里取的是@i,而@i屬于會(huì)話變量而已）
-- ORDER BY time

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

生成最近12個(gè)月

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`

總結(jié)

到此這篇關(guān)于使用MySQL生成最近24小時(shí)整點(diǎn)時(shí)間臨時(shí)表的文章就介紹到這了,更多相關(guān)mysql生成最近時(shí)間臨時(shí)表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: