MySQL根据某个或多个字段查询重复数据的SQL

MySQL根据某个或多个字段查询重复数据的SQL

1.表中有id和name两个字段，查询出name重复的所有数据

select * from user a where (a.username) in (select username from user group by username having count(*) > 1)

2.查询出所有数据进行分组之后，和重复数据的重复次数的查询数据

select count(username) as '重复次数',username from user group by username having count(*)>1 order by username desc

3.查找表中多余的重复记录，重复记录是根据单个字段openid来判断

select * from user where openid in ( select openid from user group by openid having count(*) > 1)

4.查找表中多余的重复记录，重复记录是根据单个字段openid来判断

select * from user where (openid,nickname) in ( select openid,nickname from user group by openid,nickname having count(*) > 1)