首页 > 代码库 > mysql删除重复记录，保存Id最小的一条
mysql删除重复记录，保存Id最小的一条
方法1：1、创建一个临时表，选取需要的数据。2、清空原表。3、临时表数据导入到原表。4、删除临时表。mysql> select * from student;+----+------+| ID | NAME |+----+------+| 11 | aa || 12 | aa || 13 | bb || 14 | bb || 15 | bb || 16 | cc |+----+------+6 rows in setmysql> create temporary table temp as select min(id),name from student group by name;Query OK, 3 rows affectedRecords: 3 Duplicates: 0 Warnings: 0mysql> truncate table student;Query OK, 0 rows affectedmysql> insert into student select * from temp;Query OK, 3 rows affectedRecords: 3 Duplicates: 0 Warnings: 0mysql> select * from student;+----+------+| ID | NAME |+----+------+| 11 | aa || 13 | bb || 16 | cc |+----+------+3 rows in setmysql> drop temporary table temp;Query OK, 0 rows affected这个方法，显然存在效率问题。方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：mysql> create temporary table temp as select min(id) as MINID from student group by name;Query OK, 3 rows affectedRecords: 3 Duplicates: 0 Warnings: 0mysql> delete from student where id not in (select minid from temp);Query OK, 3 rows affectedmysql> select * from student;+----+------+| ID | NAME |+----+------+| 11 | aa || 13 | bb || 16 | cc |+----+------+3 rows in set 方法3：直接在原表上操作，容易想到的sql语句如下：mysql> delete from student where id not in (select min(id) from student group by name);执行报错：1093 - You can‘t specify target table ‘student‘ for update in FROM clause原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。怎么规避这个问题？再加一层封装，如下：mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);Query OK, 3 rows affectedmysql> select * from student;+----+------+| ID | NAME |+----+------+| 11 | aa || 13 | bb || 16 | cc |+----+------+3 rows in sethttp://www.cnblogs.com/nzbbody/p/4470638.html
mysql删除重复记录，保存Id最小的一条
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。