MySQL查询数据库中重复的记录几条sql

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);

您可能感兴趣的文章：
MySQL查询数据库中重复的记录几条sql
总结SQL查询重复记录并删除的方法【老师说值得收藏】
mysql随机查询大量数据的sql语句性能分析
mysql慢查询开启与配置
mysql全文查询与随机查询的例子
分享：mysql随机查询若干条数据的方法
mysql 随机生成某个范围内的整数
学习mysql的查询缓存
有关 mysql 慢查询日志
mysql取随机数据的方法