MySQL教程之MySQL删除重复数据只保留一条
发布时间：2022-08-10 10:19:25 所属栏目：MySql教程 来源：互联网
导读：如何从student表中删除重复名字的行，并保留最小id的记录？ 1、删除表中多余的重复记录，重复记录是username判断，只留有id最小的记录 delete from studentwhere username in ( select username from studentgroup by username having count(username)1) and
|
如何从student表中删除重复名字的行，并保留最小id的记录？ 1、删除表中多余的重复记录，重复记录是username判断，只留有id最小的记录 delete from studentwhere username in ( select username from studentgroup by username having count(username)>1) and id not in (select min(id) as id from studentgroup by username having count(username)>1 ) (上面这条语句在mysql中执行会报错： 执行报错：1093 - You can't specify target table 'student' for update in FROM clause 原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。oracel和msserver都支持这种方式。 怎么规避这个问题？ 再加一层封装, delete from student where username in (select username from ( select username from student group by username having count(username)>1) a) and id not in ( select id from (select min(id) as id from student group by username having count(username)>1 ) b) 注意select min(id) 后面要有as id. 其实还有更简单的办法（针对单个字段）： delete from student where id not in (select id from (select min(id) as id from student group by username) b); 拓展： 2、删除表中多余的重复记录（多个字段），只留有id最小的记录 delete from student a where (a.username,a.seq) in (select username,seq from (select username,seq from a group by username,seq having count(*) > 1) t1) and id not in ( select id from (select min(id) from vitae group by username,seq having count(*)>1) t2) （编辑：宣城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
