MySQL查询表内重复记录

在IT行业这个发展更新速度很快的行业，只有不停止的学习，才不会被行业所淘汰。如果你是数据库学习者，那么本文《MySQL查询表内重复记录》就很适合你！本篇内容主要包括MySQL查询表内重复记录，希望对大家的知识积累有所帮助，助力实战开发！

1、查找表中多余的重复记录，重复记录是根据单个字段（user_id）来判断

select * from tbl_user where user_id in (select user_id from people group by user_id having count(user_id) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（user_id）来判断，只留有一个记录

delete from tbl_user where user_id in (select user_id from people group by user_id having count(user_id) > 1) and min(id) not in (select id from people group by user_id having count(user_id)>1)

3、查找表中多余的重复记录（多个字段）

select * from table where (user_id,lesson_id) in (select user_id,lesson_id from table group by user_id,lesson_id having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有id最小的记录

delete from table where (user_id,lesson_id) in (select user_id,lesson_id from table group by user_id,lesson_id having count(*) > 1) and id not in (select min(id) from table group by user_id,lesson_id having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含id最小的记录

select * from table where (user_id,lesson_id) in (select user_id,lesson_id from table group by user_id,lesson_id having count(*) > 1) and id not in (select min(id) from table group by user_id,lesson_id having count(*)>1)

今天带大家了解了MySQL的相关知识，希望对你有所帮助；关于数据库的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~