网站建设接活,本地wordpress,wordpress 分类页评论框,汝州市城乡建设局网站因为要增加需求#xff0c;需要将线上表中老数据#xff0c;修改为新数据的规则。 线上两张表#xff0c;sequence_number中is_use有3作废、2到期状态#xff0c;需要根据这个状态和school_ai_authorization中的is_deleted修改新增的state字段。 sequence_number表结构需要将线上表中老数据修改为新数据的规则。 线上两张表sequence_number中is_use有3作废、2到期状态需要根据这个状态和school_ai_authorization中的is_deleted修改新增的state字段。 sequence_number表结构蓝色为重要查询字段。 school_ai_authorization表 当然细心的你看到了sequence_number_id为什么没有用这个因为数据库没有这个字段的数据。尴尬吧
错误的查询方式
select saa.* from la_school_ai_authorize saa where saa.is_deleted 1
and saa.textbook_level_id in
(select textbook_level_id from la_sequence_number where is_use 2)
and saa.school_id in
(select school_id from la_sequence_number where is_use 2)
and saa.telephone in
(select telephone from la_sequence_number where is_use 2)in1,2in3,4in5,6因为三个in的意思是只要在后边的数据中就行不是数据过滤。
正确用in
select saa.* from la_school_ai_authorize saa where saa.is_deleted 1
and (saa.textbook_level_id,saa.school_id,saa.telephone) in
(select textbook_level_id,school_id,telephone from la_sequence_number where is_use 3)这才是是根据sequence_number中到期数据查询school_ai_authorize中被删除的数据是因为序列号到期还是作废。
还有个更恶心的东西sequence_number中textbook_level_id并不只在这个字段中存在还有个“”拼接到textbook_level_ids字段中所以这个也要查询出来。这里用到了find_in_set(字段textbook_level_ids) 0 如果存在返回在textbook_level_ids中的位置。
SELECT saa.*
FROM la_school_ai_authorize saa
WHERE saa.is_deleted 1 and
EXISTS (SELECT 1 FROM la_sequence_number snWHERE sn.is_use 2 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) 0 or saa.textbook_level_id sn.textbook_level_id)and sn.school_id saa.school_id and sn.telephone saa.telephone
);但是find_in_set查询效率比较慢量大查询很长时间所以可以用关联查询优化子查询。或者优化表结构ids就不该存在。或者加索引。
#用多表联查优化子查询。
SELECT DISTINCT saa.*
FROM la_school_ai_authorize saa INNER JOIN la_sequence_number sn
on sn.school_id saa.school_id and sn.telephone saa.telephone and sn.is_use 2 and saa.is_deleted 1 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) 0 or saa.textbook_level_id sn.textbook_level_id)记得用distinct不然数据会多的。
根据条件修改表
#is_use 3 作废 state 2
update la_school_ai_authorize_copy1 saa set saa.state 2, saa.is_deleted 0
WHERE saa.is_deleted 1 and
EXISTS (SELECT 1 FROM la_sequence_number snWHERE sn.is_use 3 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) 0 or saa.textbook_level_id sn.textbook_level_id)and sn.school_id saa.school_id
);
#is_use 2 已到期 state 3
update la_school_ai_authorize_copy1 saa set saa.state 3, saa.is_deleted 0
WHERE saa.is_deleted 1 and
EXISTS (SELECT 1 FROM la_sequence_number snWHERE sn.is_use 2 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) 0 or saa.textbook_level_id sn.textbook_level_id)and sn.school_id saa.school_id and sn.telephone saa.telephone
);
# 剩余是解绑的
