mysql 临时表 实例_Mysql 临时表示例

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