登录 |  注册 |  繁體中文


mysql查询及删除重复记录

分类: 数据库 颜色:橙色 默认  字号: 阅读(1639) | 评论(0)

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,
delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,并且只留peopleId最小的记录
delete from people
where peopleId  in (select  max(peopleId)  from people  group  by  peopleId   having  count(peopleId) > 1)


3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)

4、删除表中多余的重复记录(多个字段),
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)


(二)
比方说
在A表中存在一个字段“name”,
而且不同记录之间的“name”值有可能会相同,
现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1


(三)
如果表中重复记录比较多,则重建表比较方便
create table A2 select * from A Group By Name;




姓 名: *
邮 箱:
内 容: *
验证码: 点击刷新 *   

回到顶部