萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> sql 刪除重復數據

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

 

 

copyright © 萬盛學電腦網 all rights reserved