mysql 定时执行 查询动态表名插入汇总表的sql
DELIMITER //
CREATE EVENT IF NOT EXISTS monthdata_total_task
ON SCHEDULE
EVERY 6 hour
STARTS CURRENT_TIMESTAMP -- 明确指定事件启动时间为当前时间
ON COMPLETION PRESERVE
DO
BEGIN
TRUNCATE TABLE monthdata_total;
SET @tableName = CONCAT(DATE_FORMAT(NOW(), '%Y%m'), '_monthdata_3205010004');
SET @sql = CONCAT(
"INSERT INTO monthdata_total (sign, funcid, receivetime, number)
SELECT t2.sign, t2.funcid, t2.receivetime, t2.data
FROM (
SELECT sign, funcid, MAX(receivetime) AS max_receivetime
FROM (SELECT DISTINCT sign, funcid, receivetime, `data` FROM ", @tableName, " ) t1
WHERE funcid = '10101'
GROUP BY sign, funcid
) AS t1
JOIN ", @tableName, " AS t2
ON t1.sign = t2.sign AND t1.max_receivetime = t2.receivetime AND t1.funcid = t2.funcid
WHERE t2.funcid != '999999'"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END; //
DELIMITER ;