记录原因:
昨天遇到一个问题：需要写一个sql语句删除重复数据。当时解决的办法,是先查询出重复的数据id，然后再单独写删除语句进行删除。今天想了想应该还是有其他办法解决的，研究了下可以通过一条语句就能解决。记录下顺便做下对比:
单独写查询和删除方法:
1.先查询出重复数据中非最小的id:select t.* from ukey t where t.id not in (select min(t1.id) from ukey t1 group by t1.ter_idf,cast(t1.ter_portf as SIGNED INTEGER ));2.再把查询出来的id删除:delete from ukey where id in (id...);
优化之后变一条语句:
delete t.* from ukey as twhere t.id in (select * from (select id as min_id from ukey where id not in (select min(id) from ukey group by ter_idf,cast(ter_portf as SIGNED INTEGER ))) as d);
备注: 在优化成一条语句的时候遇到了下面的问题:
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause
大概意思是：不能在一条语句内对同一张表查询出来的参数进行修改。
后来查看相关资料，解决了问题！！！
官网资料:
/doc/refman/5.6/en/update.html
其他资料:
/u012767761/article/details/84997962/
如果觉得《利用sql语句删除重复数据》对你有帮助，请点赞、收藏，并留下你的观点哦！
