Mysql 临时表示例

业务场景

某表里的数据是每整点时刻存入一次，但是有可能漏掉某个时刻，比如2019-04-27 14:00:00有数据，有可能上一个整点2019-04-27 13:00:00没有数据。

需求

查询出24小时内该表所有的数据并返回给前端进行Echarts图表展示

问题

如果数据全部正常的话，查出来应该是24条，但是极大情况是不够24条，这就需要增加空白行，即使这个空白行记录的时间在表里没有对应。

解决办法

创建临时表，并insert24小时段，临时表在会话结束时会自动删除。
CREATE TEMPORARY TABLE tmp ( format_data VARCHAR ( 50 ) NOT NULL );
INSERT INTO tmp ( format_data )
VALUES
	( DATE_FORMAT( ( NOW( ) - INTERVAL 24 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 23 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 22 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 21 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 20 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 19 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 18 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 17 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 16 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 15 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 14 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 13 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 12 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 11 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 10 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 9 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 8 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 7 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 6 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 5 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 4 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 3 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 2 HOUR ), '%Y-%m-%d %H:00:00' ) ),
	( DATE_FORMAT( ( NOW( ) - INTERVAL 1 HOUR ), '%Y-%m-%d %H:00:00' ) );
SELECT
	a.* 
FROM
	tmp
	LEFT JOIN (
	SELECT
		sta_basic_info.STA_NUM,
		sta_basic_info.STA_NAME,
		dat_aws_hour_qc.OBSERVE_TIME
	FROM
		dat_aws_hour_qc
		LEFT JOIN sta_basic_info ON sta_basic_info.STA_NUM = dat_aws_hour_qc.STATION_CODE 
		AND sta_basic_info.STA_TYPE = '4' 
	WHERE
		dat_aws_hour_qc.OBSERVE_TIME >= ( NOW( ) - INTERVAL 24 HOUR ) 
		AND dat_aws_hour_qc.STATION_CODE = '50442' 
	ORDER BY
		dat_aws_hour_qc.STATION_CODE,
		dat_aws_hour_qc.OBSERVE_TIME 
	) a ON tmp.format_data = a.OBSERVE_TIME 
ORDER BY
	tmp.format_data