mysql删除重复记录并且只保留一条

mysql删除重复记录并且只保留一条

-- 创建表
create table employees(
eid int,
ename varchar(20)
)
-- 添加数据
insert into employees values
(1,'陈平安'),
(2,'陈平安'),
(3,'宁姚'),
(4,'宁姚')，
(5,'虎头白也'),
(6,'魔道正剧中'),
(7,'绣虎崔巉'),
(8,'春风齐静春')

1.查询表中名称重复的数据

SELECT ename from employees GROUP BY ename HAVING COUNT(1)>1

2.查询出重复数据的id

SELECT aa.eid from employees aa where aa.ename in
(SELECT a.ename from employees a GROUP BY a.ename HAVING COUNT(1)>1)

3.根据name分组查询出id 最小的

SELECT min(aa.eid) from employees aa where aa.ename in
(SELECT a.ename from employees a GROUP BY a.ename HAVING COUNT(1)>1) GROUP BY ename

4.使用not in 找出要删除的id，并存入临时表中

CREATE TABLE lshi(
SELECT aa.eid from employees aa where aa.ename in
(SELECT a.ename from employees a GROUP BY a.ename HAVING COUNT(1)>1) and aa.eid not in 
(
SELECT min(aa.eid) from employees aa where aa.ename in
(SELECT a.ename from employees a GROUP BY a.ename HAVING COUNT(1)>1) GROUP BY ename
))
)

5.关联临时表删除即可

DELETE from employees a where a.eid in(SELECT * from lshi)

 小菜一枚，有什么写的不妥的地方，欢迎大家指教！

 我会不定期的看评论，也会根据大家的指点来修改博客不妥的地方！

 各位读者有什么好的方法，或者想法    也欢迎各位大牛到评论区留言。。。