表t_repo_plate中有相同车牌号不同车牌颜色的数据,但这些车牌其实只有1种颜色,说明有脏数据。t_repo_plate中的数据会不断更新。
[1]先修改车牌颜色错误的数据:
注:8其实应改为1
update t_repo_plate t4
INNER JOIN (
select t1.plate
from (select plate from t_repo_plate where pcolor = 8) t1
left join (select plate from t_repo_plate where pcolor = 1) t2 on t1.plate = t2.plate
where t2.plate is null
) t3
ON t4.plate = t3.plate
set t4.pcolor = 1
where t4.pcolor = 8;
[2]经过以上面的处理后表中剩下的车牌颜色为8的是改不了数据,再把这些车牌颜色为8的数据删除:
delete from t_repo_plate where pcolor = 8;
[3]有的数据是同一个车牌既有0又有255,但255是错误的数据,要删除255这条并保留0这条:
DELETE t
FROM t_repo_plate t
Inner JOIN (
select t1.plate, t1.pcolor
from t_repo_plate t1
inner join (select plate from t_repo_plate where pcolor = 0) t2 on t1.plate = t2.plate
where t1.pcolor = 255
) t3 on t.pcolor = t3.pcolor and t.plate = t3.plate;
备注:留档自查!