实验环境：CENTOS:6.8
MSYQL:5.7.20

1、创建表test001

点击(此处)折叠或打开

CREATE TABLE `test001` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2、随机写入数据导表test001中
    insert into test001 (name) values('A');
    insert into test001 (name) values('B');
    insert into test001 (name) values('C');
    insert into test001 (name) values('d');

3、查询整表数据

点击(此处)折叠或打开

select * from test001;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | A |
| 5 | A |
| 6 | A |
| 7 | A |
| 8 | A |
| 9 | B |
| 10 | B |
| 11 | B |
| 12 | B |
| 13 | B |
| 14 | B |
| 15 | C |
| 16 | C |
| 17 | C |
| 18 | C |
| 19 | d |
| 20 | d |
| 21 | d |
| 22 | d |
| 23 | d |
+----+------+
23 rows in set (0.00 sec)

4、执行SQL删除重复记录，只保留id最小的记录

DELETE FROM Test001 WHERE id NOT IN (

SELECT minid FROM

(SELECT min(id) AS minid FROM Test001

GROUP BY name) b

);

点击(此处)折叠或打开

>DELETE
-> FROM
-> Test001
-> WHERE
-> id NOT IN (
-> SELECT
-> minid
-> FROM
-> (
-> SELECT
-> min(id) AS minid
-> FROM
-> Test001
-> GROUP BY
-> name
-> ) b
-> );
Query OK, 19 rows affected (0.00 sec)
(root@localhost:mysql.sock) [test]>select * from test001;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 9 | B |
| 15 | C |
| 19 | d |
+----+------+
4 rows in set (0.00 sec)

5、执行后的结果，重复记录都删除了

点击(此处)折叠或打开

(root@localhost:mysql.sock) [test]>select * from test001;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 9 | B |
| 15 | C |
| 19 | d |
+----+------+
4 rows in set (0.00 sec)

ITPub博客

MySQL 删除单表重复记录SQL

最新文章