sql 删除重复数据

sql 删除重复数据
本文章提供一款mysql删除重复数据，就是数据表中存在二条以上的重复数据，删除编号最小的一条信息。
delete `borough_name` as a from fke_borough as a,
(
select *,min(id) from fke_borough group by `borough_name` having count(1) > 1
) as b
 where a.`borough_name` = b.`borough_name` and a.id > b.id;

SELECT count( * )
FROM `fke_borough` //3423

SELECT * FROM `fke_borough` group by `borough_name` HAVING count(`borough_name`)>1//重复591条

查询重复记录 select * from table GROUP BY name

只把有重复的显示出来
select * ,count(*) as nums from tab_a group by name having nums>1

方法一：（这个方法比较不错，只是自增字段会重建）

  新建一个临时表 
  create   table   tmp   as   select   *   from   youtable   group   by   name 
  删除原来的表 
  drop   table   youtable 
  重命名表 
  alter   table   tmp   rename   youtable