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