mysql查询数据库重复数据库_MySQL查询数据库中重复的记录

1.根据表中单个字段(name)来查询重复记录

SELECT * FROM user WHERE name IN ( SELECT name FROM user GROUP BY name HAVING COUNT(name) > 1 );

2.根据表中单个字段(name)，删除重复记录，只保留id最小的记录

DELETE FROM user WHERE name IN ( SELECT name FROM user GROUP BY name HAVING COUNT(name) > 1 ) AND id NOT IN (SELECT min(id) FROM user GROUP BY name HAVING COUNT(name) > 1);

3.根据表中多个字段(name, age)来查询重复记录

SELECT * FROM user u WHERE (u.name, u.age) IN ( SELECT name, age FROM user GROUP BY name, age HAVING COUNT(*) > 1 );

4.根据表中多个字段(name, age)，删除重复记录，只保留id最小的记录

DELETE FROM user u WHERE (u.name, u.age) IN ( SELECT name, age FROM user GROUP BY name, age HAVING COUNT(*) > 1 ) AND id NOT IN (SELECT min(id) FROM user GROUP BY name, age HAVING COUNT(*) > 1);

转载时请注明出处及相应链接，本文永久地址：https://blog.yayuanzi.com/13930.html

微信打赏

支付宝打赏

感谢您对作者Sun的打赏，我们会更加努力！    如果您想成为作者，请点我