创建临时表,并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