面试碰到一个MySQl的有趣的题目,如何从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)
参考文章:
https://blog.csdn.net/anya/article/details/6407280
如对本文有疑问, 点击进行留言回复!!
MySql添加新用户及为用户创建数据库和给用户分配权限方法介绍
Navicat出现无法远程连接MySql服务器问题的解决办法
在Ubuntu/Linux环境下使用MySQL开放/修改3306端口和开放访问权限
ORM框架Peewee(高级连接)peewee连接池、主从、重连、ssh连接
网友评论