MySql select查询表中重复记录的SQL

数据重复可以表现为某几个字段重复,也可以表示为全部字段都重复了(没有主键的情况下)

查询某一字段的重复项：
select user_name,count(*) as count from user group by user_name having count>1;

查询多个字段字段的重复项：
select * from user t where (t.id,t.`name`,t.age) in (select a.id,a.`name`,a.age from user a group by a.id,a.`name`,a.age having count(*) > 1)

当数据量大的时候建议时间join(尽量少用in like '%')：
select * from user inner join (select id,`name` from user group by id,`name` having count(*)>1) as tab2 using (id,`name`);

转载于:https://www.cnblogs.com/molao-doing/articles/8856348.html