MySql删除重复数据只保留最小id的那条数据。某某公司的临时面试题

本文主要是介绍MySql删除重复数据只保留最小id的那条数据。某某公司的临时面试题，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

错误代码：

DELETE 
FROMpayment 
WHEREserial IN ( SELECT serial FROM payment GROUP BY serial HAVING count(*) > 1 ) AND id NOT IN ( SELECT min( id ) AS id FROM payment GROUP BY serial HAVING count( serial ) > 1 );

错误提示：

 

正确代码： 使用临时表

DELETE 
FROMpayment 
WHEREserial IN ( select serial from  (SELECT serial FROM payment GROUP BY serial HAVING count(*) > 1) a ) AND id NOT IN ( select id from (SELECT min( id ) as id  FROM payment GROUP BY serial HAVING count( serial ) > 1)b  );

效果：

实验前数据

执行sql： 

 

这篇关于MySql删除重复数据只保留最小id的那条数据。某某公司的临时面试题的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---