MySQL中怎样删除表重复记录
发布时间：2022-01-21 12:45:23 所属栏目：MySql教程 来源：互联网
导读：本篇文章给大家分享的是有关MySQL中如何删除表重复记录，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 创建实验表student表数据: mysql use test Database changed mysql create table s
|
本篇文章给大家分享的是有关MySQL中如何删除表重复记录，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 创建实验表student表数据: mysql> use test Database changed mysql> create table student (id int,name varchar(10)); Query OK, 0 rows affected (1.67 sec) mysql> insert into student values (11,'aa'); Query OK, 1 row affected (0.26 sec) mysql> insert into student values (12,'aa'); Query OK, 1 row affected (0.07 sec) mysql> insert into student values (13,'aa'); Query OK, 1 row affected (0.12 sec) mysql> insert into student values (14,'aa'); Query OK, 1 row affected (0.11 sec) mysql> insert into student values (15,'bb'); Query OK, 1 row affected (0.19 sec) mysql> insert into student values (16,'bb'); Query OK, 1 row affected (0.14 sec) mysql> insert into student values (17,'cc'); Query OK, 1 row affected (0.15 sec) mysql> select * from student; +------+------+ | id | name | +------+------+ | 11 | aa | | 12 | aa | | 13 | aa | | 14 | aa | | 15 | bb | | 16 | bb | | 17 | cc | +------+------+ 7 rows in set (0.22 sec) 方法1: mysql> create temporary table temp as select min(id),name from student group by name; Query OK, 3 rows affected (0.18 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> truncate table student; Query OK, 0 rows affected (0.40 sec) mysql> insert into student select * from temp; Query OK, 3 rows affected (0.11 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student; +------+------+ | id | name | +------+------+ | 11 | aa | | 15 | bb | | 17 | cc | +------+------+ 3 rows in set (0.00 sec) mysql> drop temporary table temp; Query OK, 0 rows affected (0.17 sec) 方法2: mysql> create temporary table temp as select min(id) as MINID from student group by name; Query OK, 3 rows affected (0.24 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from student where id not in (select minid from temp); Query OK, 4 rows affected (0.07 sec) mysql> select * from student; +------+------+ | id | name | +------+------+ | 11 | aa | | 15 | bb | | 17 | cc | +------+------+ 3 rows in set (0.00 sec) 方法3： mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b); Query OK, 4 rows affected (0.19 sec) mysql> select * from student; +------+------+ | id | name | +------+------+ | 11 | aa | | 15 | bb | | 17 | cc | +------+------+ 3 rows in set (0.00 sec) 以上就是MySQL中如何删除表重复记录，小编相信有部分知识点可能是我们日常工作会见到或用到的。 （编辑：91站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
