-- 数据修复 -- 需备份 evaluation_item_used 表数据 WITH t1 AS ( SELECT evaluation_project.company_id, evaluation_project.id AS project_id, jsonb_array_elements(evaluation_project."template" #> '{linkNodes}') AS nodes FROM evaluation_project WHERE evaluation_project."template" #> '{linkNodes}'<>'null' ORDER BY id ), t2 AS ( SELECT t1.company_id, t1.project_id, t1.nodes ->> 'id' AS node_id, t1.nodes ->> 'type' AS node_type, jsonb_array_elements(t1.nodes #> '{nodeContents}') AS node_contents FROM t1 WHERE t1.nodes #> '{nodeContents}'<>'null' ), t3 AS( SELECT t2.company_id, t2.project_id, cast(t2.node_id AS int8) AS node_id, cast(t2.node_type AS int8) AS node_type, t2.node_contents ->> 'category' AS "category", t2.node_contents ->> 'name' AS "name", cast(t2.node_contents ->> 'ruleId' AS int8) AS "rule_id" FROM t2 ), t4 as ( SELECT t3.project_id, t3.node_type, t3."category", t3."name", row_to_json( "evaluation_rule".*) as "rule", "evaluation_rule"."type" as rule_type FROM t3 join "evaluation_rule" on t3.rule_id= "evaluation_rule".id ), t5 as ( SELECT evaluation_item_used."id", evaluation_item_used.evaluation_project_id, evaluation_item_used.node_type, evaluation_item_used.category, evaluation_item_used."name" FROM evaluation_item_used WHERE "rule"='{}' or "rule" ISNULL ), t6 as ( SELECT t5."id" , t4."rule", t4.rule_type FROM t5 ,t4 WHERE t5.evaluation_project_id=t4.project_id and t5.node_type=t4.node_type and t5.category=t4.category and t5."name"=t4."name" ) update evaluation_item_used set "rule"=t6."rule", rule_type=t6."rule_type" FROM t6 WHERE evaluation_item_used."id"=t6."id" ; -- 修复数据 with t1 as ( SELECT evaluation_rule.id::TEXT, evaluation_rule."name", evaluation_rule.remark, evaluation_rule.company_id::TEXT as "companyId", evaluation_rule.creator_id::TEXT as "creatorId", evaluation_rule."type", evaluation_rule.rating, evaluation_rule.score, evaluation_rule.sys_type as "sysType" FROM evaluation_rule ), t2 as ( SELECT t1.id, row_to_json(t1.*) as row_j FROM t1 ), t3 as ( SELECT evaluation_item_used.id, t2.row_j FROM evaluation_item_used join t2 on t2.id= evaluation_item_used."rule"->>'id' WHERE evaluation_item_used."rule"->'companyId' ISNULL and evaluation_item_used."rule"->'id' is not null ) update evaluation_item_used SET "rule"=t3.row_j FROM t3 WHERE evaluation_item_used.id = t3.id ; -- 修改开始结束时间 UPDATE summary_evaluation set begin_time = '2023-03-31 23:59:00+08', end_time = '2023-04-02 23:59:00+08' WHERE id=36 ; UPDATE summary_evaluation set begin_time = '2023-04-02 23:59:00+08', end_time = '2023-04-04 23:59:00+08' WHERE id=37 -- 修改数据summary_evaluation 表数据 with t1 as ( SELECT summary_evaluation.id,summary_evaluation.evaluation_project_id FROM summary_evaluation WHERE to_char( summary_evaluation.begin_time, 'YYYY-MM-DD') ='2023-03-31' and to_char( summary_evaluation.end_time, 'YYYY-MM-DD') ='2023-04-02' and company_id=1 ) update summary_evaluation set end_time=end_time+'1 day' FROM t1 WHERE summary_evaluation.evaluation_project_id=t1.evaluation_project_id