【www.gdgbn.com--mysql教程】
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