MySQL查询出所有重复元素

一.

1)查询以单个字段为标准

SELECT * FROM student WHERE shortname IN (SELECT shortname FROM student GROUP BY shortname HAVING COUNT(shortname)>1)//查找出所有的重复数据

2)查看重复数据的条数

SELECT shortname,COUNT(*) FROM student GROUP BY shortname HAVING COUNT(*) > 1//查看重复数据的条数

二.

group by方法删除重复元素

查数据:
select count(num), max(name) from student --列出重复的记录数，并列出他的name属性
group by num
having count(num) >1 --按num分组后找出表中num列重复，即出现次数大于一次
删数据：
delete from student
group by num
having count(num) >1
这样的话就把所有重复的都删除了。

三

查询及删除重复记录的方法大全
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

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

删除重复元素