欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql查找删除重复数据,只保留一条

程序员文章站 2022-06-02 15:54:54
...

mysql查找删除重复数据,只保留一条

我们可以看出,name字段有重复数据,(华师一附中出现了两次),如何删除重复数据保留其中的一条呢?具体实现方案如下:

1.删除重复字段,保留id最小的一条

delete FROM `t_school_name` WHERE 
`name` in 
(select name from (SELECT `name` FROM `t_school_name` GROUP BY `name` HAVING COUNT( * ) >1) ) 
and id not in 
(select id from (select min(id)  from t_school_name group by name having count(* )>1) )

  先使用 group by having count(1)>1找出重复字段,然后删除数据,保留id最小的数据.

2.删除重复字段,保留id最大的以调

delete FROM `t_school_name` WHERE 
`name` in 
(select name from (SELECT `name` FROM `t_school_name` GROUP BY `name` HAVING COUNT( * ) >1) ) 
and id not in 
(select id from (select max(id)  from t_school_name group by name having count(* )>1) )
先使用 group by having count(1)>1找出重复字段,然后删除数据,保留id最大的数据.


相关标签: 删除重复数据