mysql删除重复数据，并保留重复数据中最小id所在行的数据

原始数据库 usename重复数据删除

第一步 找出username 字段有重复值 且count >1的username值

第二步 username字段有重复且count>1数据的最小id

显然就是以name为条件删除 id 不在第二步里面的所有数据 我们拼接sql语句

 

 

结果

 

#第一步 找出username 字段有重复值 且count >1的username值
select username as dname from employee group by username having count(username) > 1

-- 第二步 cv 上面的 在重复数据中找出最小id的数据
select min(id) as mid from employee group by username having count(username) > 1;

-- 拼接sql语句 删除第一步 名字里面的所有数据 除了各自id最小的数据  实现删除 

sql语句 如下 

#第一步 找出username 字段有重复值 且count >1的username值
select username as dname from employee group by username having count(username) > 1

-- 第二步 cv 上面的 在重复数据中找出最小id的数据
select min(id) as mid from employee group by username having count(username) > 1;

-- 拼接sql语句 删除第一步 名字里面的所有数据 除了各自id最小的数据  实现删除 
delete from employee

where

username in (select pname from (select username as pname from employee group by username having count(username) > 1) a)

and

id not in (select pid from (select min(id) as pid from employee group by username having count(username) > 1 ) b)

-- 完事 设置表单username唯一 
alter table  employee add unique(username);