MySQL查询重复数据与去重

一、创建表，插入一批示例数据

create table student(
	id bigint(20) auto_increment,
	code int(20),
	name varchar(255),
	flag int(2),
	primary key(id)
)engine=innodb;

insert into student values(1,100,"小李",0);
insert into student values(2,100,"小李",0);
insert into student values(3,100,"小李",0);
insert into student values(4,101,"小张",0);
insert into student values(5,102,"小孙",0);
insert into student values(6,102,"小孙",0);
insert into student values(7,103,"小赵",0);
insert into student values(8,104,"小马",0);
insert into student values(9,105,"小飞",0);
insert into student values(10,105,"小飞",0);

二、根据学生编号和姓名查询重复数据

ps：该班级没有姓名相同的学生（拒绝杠精~~）

select name,count(*) name_count from student group by code,name having name_count > 1;

结果集：查询出所有重复的姓名和数量。

三、过滤重复的数据

方法1、select distinct code,name from student;
方法2、select code,name from student group by code,name;

四、删除重复数据

ps：一般删除重复数据是给数据标志位打标，尽量少用delete。

方法1：将重复数据的标志位打9（废弃）, 保留最小ID的数据。

UPDATE student set flag = 9 where `name` in
(select s_name from (select `name` s_name from student group by name having count(1) > 1) temp_1)
and id not in
(select id from (select MIN(ID) id from student group by name having count(1) > 1) temp_2);

方法2：将不重复的数据插入到新表中

新建表：create table student_temp select * from student group by code,name;
删除表：drop table student;
改表名：alter table student_temp rename to student;

方法3：直接在原始表中删除重复数据（慎用！！！）

delete from student where id not in(select * from (select id from student group by code,name)a)