MySQL（11）：重复记录相关的问题

去重查询

select distinct(xxx字段) from student;

还可以用group by!!!

select * from student group by 有重复记录的字段;

查询出有重复的学生姓名

select name from student group by name having count(name) > 1;

删掉所有重复的记录

delete from student where name in select name from student group by name having count(name) > 1;

删除重复姓名的记录，但保留id小的记录

任务分解

1. 查询重复姓名的记录

select name from student group by name having count(name) > 1;

2. 查询出重复记录中id小的

select min(stu_id) from student group by name having count(name) > 1;

3. 删除记录有两个条件

条件1：姓名在第1步的查询结果中；

条件2：id不在第2步的查询结果中。

delete from student where ( (name in select name from student group by name having count(name) > 1) and 

(id not in select min(stu_id) from student group by name having count(name) > 1) );