MySQL 删除单表重复记录SQL

实验环境：CENTOS:6.8
                 MSYQL:5.7.20

1、创建表test001

点击(此处)折叠或打开

1. CREATE TABLE `test001` (
   
2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   
3.   `name` varchar(20) NOT NULL,
   
4.   PRIMARY KEY (`id`)
   
5. ) 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、查询整表数据

点击(此处)折叠或打开

1. select * from test001;
   
2. +----+------+
   
3. | id | name |
   
4. +----+------+
   
5. | 1 | A |
   
6. | 2 | A |
   
7. | 3 | A |
   
8. | 4 | A |
   
9. | 5 | A |
   
10. | 6 | A |
    
11. | 7 | A |
    
12. | 8 | A |
    
13. | 9 | B |
    
14. | 10 | B |
    
15. | 11 | B |
    
16. | 12 | B |
    
17. | 13 | B |
    
18. | 14 | B |
    
19. | 15 | C |
    
20. | 16 | C |
    
21. | 17 | C |
    
22. | 18 | C |
    
23. | 19 | d |
    
24. | 20 | d |
    
25. | 21 | d |
    
26. | 22 | d |
    
27. | 23 | d |
    
28. +----+------+
    
29. 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

);

点击(此处)折叠或打开

1. >DELETE
   
2.     -> FROM
   
3.     -> Test001
   
4.     -> WHERE
   
5.     -> id NOT IN (
   
6.     -> SELECT
   
7.     -> minid
   
8.     -> FROM
   
9.     -> (
   
10.     -> SELECT
    
11.     -> min(id) AS minid
    
12.     -> FROM
    
13.     -> Test001
    
14.     -> GROUP BY
    
15.     -> name
    
16.     -> ) b
    
17.     -> );
    
18. Query OK, 19 rows affected (0.00 sec)
    
19. 
    
20. (root@localhost:mysql.sock) [test]>select * from test001;
    
21. +----+------+
    
22. | id | name |
    
23. +----+------+
    
24. | 1 | A |
    
25. | 9 | B |
    
26. | 15 | C |
    
27. | 19 | d |
    
28. +----+------+
    
29. 4 rows in set (0.00 sec)

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

点击(此处)折叠或打开

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987453/viewspace-2149396/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29987453/viewspace-2149396/