利用sql语句删除重复数据

记录原因:

昨天遇到一个问题：需要写一个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 t
where 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

大概意思是：不能在一条语句内对同一张表查询出来的参数进行修改。
后来查看相关资料，解决了问题！！！

官网资料:

https://dev.mysql.com/doc/refman/5.6/en/update.html

其他资料:

https://blog.csdn.net/u012767761/article/details/84997962/