mysql 定时执行 查询动态表名插入汇总表的sql

本文主要是介绍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 ;

这篇关于mysql 定时执行 查询动态表名插入汇总表的sql的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---