1、创建存储过程
SQL预览
DROP PROCEDURE IF EXISTS `sp_evaluation_point`; CREATE DEFINER = `root`@`%` PROCEDURE `sp_evaluation_point`() BEGIN #Routine body goes here... #定义参数 DECLARE yesterday date; DECLARE _day VARCHAR (50); DECLARE point INT (11); DECLARE begin_date VARCHAR (50); DECLARE end_date VARCHAR (50); #设置值 SET yesterday = CURDATE() - 1; SET begin_date = CONCAT(yesterday, ' 00:00:00'); SET end_date = CONCAT(yesterday, ' 23:59:59'); #数据插入到数据库 INSERT INTO st_evaluation_point ( _day, user_id, head_img, dimension_id, class_id, grade_id, point ) SELECT DATE_FORMAT( table1.evaluate_time, '%Y-%m-%d' ), table1.evaluated_user_id, table2.path, table6.dimension_id, table4.classroom_id, table5.grade_number, SUM(table1.cert) FROM web_stu_evaluate table1 LEFT JOIN web_sch_image table2 ON table2.obj_id = table1.evaluated_user_id AND table2.obj_type = '03' LEFT JOIN web_sch_student table3 ON table3.user_id = table1.evaluated_user_id LEFT JOIN web_student_classroom table4 ON table4.student_id = table3.id LEFT JOIN web_sch_classroom table5 ON table5.id = table4.classroom_id LEFT JOIN web_sch_quota table6 ON table6.id = table1.quota_id WHERE table1.evaluate_time >= begin_date AND table1.evaluate_time <= end_date GROUP BY table1.evaluated_user_id; END; 2、创建每隔一天运行存储过程的定时任务
CREATE DEFINER=`root`@`%` EVENT `NewEvent` ON SCHEDULE EVERY 1 DAY STARTS '2016-11-10 00:00:00' ON COMPLETION PRESERVE ENABLE DO call sp_evaluation_point();
