 -- !!!执行前 请先将wwyt_edu_trainning_record_user表备份!!!
 
-- 1.创建临时表
CREATE TABLE wwyt.`edu_user_test_temp` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `data_id` BIGINT(20) NOT NULL,
  `TRAINNING_RECORD_ID` BIGINT(20) NOT NULL,
  `USER_ID` BINARY(20) NOT NULL,
  `SCORE` FLOAT DEFAULT NULL,
  `TRAINNING_RESULT` ENUM('Y','N') DEFAULT NULL,
  `EVALUATION` CHAR(1) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 2.往临时表存纠正的数据
INSERT INTO wwyt.edu_user_test_temp(data_id,TRAINNING_RECORD_ID,USER_ID,SCORE,TRAINNING_RESULT,EVALUATION)
SELECT temp.id,temp.TRAINNING_RECORD_ID,temp.USER_ID,deltemp.SCORE,deltemp.TRAINNING_RESULT,deltemp.EVALUATION FROM
(SELECT * FROM wwyt_edu_trainning_record_user WHERE id IN (

SELECT MAX(id) 
FROM wwyt.`wwyt_edu_trainning_record_user` 
WHERE TRAINNING_RESULT IS NULL AND DELETE_FLAG = '0' 
GROUP BY TRAINNING_RECORD_ID,USER_ID

))temp  
INNER JOIN
(
SELECT * FROM wwyt_edu_trainning_record_user WHERE id IN (

SELECT MAX(id) 
FROM wwyt.`wwyt_edu_trainning_record_user` 
WHERE TRAINNING_RESULT IS NOT NULL AND DELETE_FLAG != '0' 
GROUP BY TRAINNING_RECORD_ID,USER_ID
)

) deltemp ON deltemp.TRAINNING_RECORD_ID = temp.TRAINNING_RECORD_ID AND deltemp.USER_ID = temp.USER_ID
WHERE deltemp.id IS NOT NULL AND temp.id IS NOT NULL;

-- 3.将临时表数据同步到wwyt_edu_trainning_record_user
UPDATE wwyt.wwyt_edu_trainning_record_user u 
INNER JOIN wwyt.edu_user_test_temp t  ON u.id =t.`data_id`
SET u.SCORE = t.SCORE,u.TRAINNING_RESULT =t.TRAINNING_RESULT,u.EVALUATION =t.EVALUATION;

-- 4.删除临时表
DROP TABLE wwyt.edu_user_test_temp;




