-- 调整 summary_evaluation_value 数据 WITH t1 AS ( SELECT summary_evaluation_value.evaluation_item_id, summary_evaluation_value.value, summary_evaluation_value.id AS value_id, summary_evaluation_value.score, summary_evaluation.company_id FROM summary_evaluation_value JOIN summary_evaluation ON summary_evaluation_value.summary_evaluation_id = summary_evaluation.id JOIN evaluation_item_used ON evaluation_item_used.id = summary_evaluation_value.evaluation_item_id WHERE summary_evaluation_value.value = summary_evaluation_value.score AND summary_evaluation."types" = 1 AND evaluation_item_used.weight > 0 ORDER BY summary_evaluation_value.created_at ), t2 AS ( SELECT t1.value_id, cast(evaluation_item_used.weight AS float) AS weight, cast(t1.value AS float) AS value, cast(t1.value AS float) * cast(evaluation_item_used.weight AS float) AS real_value FROM evaluation_item_used JOIN t1 ON t1.evaluation_item_id = evaluation_item_used.id) UPDATE summary_evaluation_value SET score = t2.real_value FROM t2 WHERE summary_evaluation_value.id = t2.value_id; -- 调整summary_evaluation 数据 WITH t1 AS ( SELECT sum(cast(summary_evaluation_value.score AS float)) AS total_score, summary_evaluation_value.summary_evaluation_id FROM summary_evaluation_value JOIN summary_evaluation ON summary_evaluation_value.summary_evaluation_id = summary_evaluation.id JOIN evaluation_item_used ON evaluation_item_used.id = summary_evaluation_value.evaluation_item_id WHERE summary_evaluation."types" = 1 AND evaluation_item_used.weight > 0 GROUP BY summary_evaluation_value.summary_evaluation_id) UPDATE summary_evaluation SET total_score = t1.total_score FROM t1 WHERE summary_evaluation.id = t1.summary_evaluation_id