mysql删除重复记录并且只保留一条

布满荆棘的人生 2024-03-30 13:36 82阅读 0赞

准备的测试表结构及数据

插入的数据中A,B,E存在重复数据,C没有重复记录

CREATE TABLE \`tab\` (  
\`id\` int(11) NOT NULL AUTO\_INCREMENT,  
\`name\` varchar(20) DEFAULT NULL,  
PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=13 DEFAULT CHARSET=utf8;  
  
\-- ----------------------------  
\-- Records of tab  
\-- ----------------------------  
INSERT INTO \`tab\` VALUES ('1', 'A');  
INSERT INTO \`tab\` VALUES ('2', 'A');  
INSERT INTO \`tab\` VALUES ('3', 'A');  
INSERT INTO \`tab\` VALUES ('4', 'B');  
INSERT INTO \`tab\` VALUES ('5', 'B');  
INSERT INTO \`tab\` VALUES ('6', 'C');  
INSERT INTO \`tab\` VALUES ('7', 'B');  
INSERT INTO \`tab\` VALUES ('8', 'B');  
INSERT INTO \`tab\` VALUES ('9', 'B');  
INSERT INTO \`tab\` VALUES ('10', 'E');  
INSERT INTO \`tab\` VALUES ('11', 'E');  
INSERT INTO \`tab\` VALUES ('12', 'E');

使用HAVING关键字筛选出表中重复数据

SELECT \`name\`,COUNT(1) FROM TAB GROUP BY \`name\` HAVING COUNT(1) >1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70][]

可以通过分组语句从每种重复数据中都拿出一条标识

SELECT \`name\`,id FROM TAB GROUP BY \`name\` HAVING COUNT(1) >1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70 1][]

删除重复记录并且只保留一条 \[留意SQL注释\]

DELETE from tab where  
\-- 删除所有的重复时间 Begin --  
\`name\` in (  
SELECT \* from (SELECT \`name\`FROM TAB GROUP BY \`name\` HAVING COUNT(1) >1) tmp2  
)  
\-- 删除所有的重复时间 END --  
\-- 但一些特定ID的记录不进行删除 Begin --  
AND  
id NOT in(  
select id from (  
SELECT \`name\`,id FROM TAB GROUP BY \`name\` HAVING COUNT(1) >1  
) tmp1  
)  
\-- 但一些特定ID的记录不进行删除 END --

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70 2][]

执行后最终结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70 3][]

方法二

## MySql如何删除所有多余的重复数据 ##

方法一查询出的所有多余的重复记录：

方法二查询出的所有多余的重复记录（与方法一的结果相同）：

方法三查询出的所有多余的重复记录：这里方法三因为用了MAX()方法（也可改用MIN()），查询结果记录的id不太一样，但也可以被视为重复多余的数据，关键是你希望选择保留哪一条记录而已。

#### MySql如何删除所有多余的重复数据 ####

*  需要处理的数据，如：

![在这里插入图片描述][a0d969f383944414ad125bc61b7647aa.png]

*  出现重复的数据，如：  
    ![在这里插入图片描述][d0cf4f1d36744cabb04c76dd5aeec66f.png]
 *  先用SELECT查询看看结果：

-- 方法一
    SELECT * FROM t_user WHERE user_name IN (
    	SELECT user_name FROM t_user GROUP BY user_name HAVING COUNT(1)>1
    ) 
    AND id NOT IN (
    	SELECT MIN(id) FROM t_user GROUP BY user_name HAVING COUNT(1)>1
    )

*  方法一查询出的所有多余的重复记录：  
    ![在这里插入图片描述][5b0a197e312a47d98711975fbc7f4f76.png]

-- 方法二
    SELECT * FROM t_user WHERE id NOT IN (
    	SELECT MIN(id) FROM t_user GROUP BY user_name
    )

*  方法二查询出的所有多余的重复记录（与方法一的结果相同）：

![在这里插入图片描述][e9b5f2db708541508941b8b89fb60f35.png]

-- 方法三
    SELECT * FROM t_user AS t1 WHERE t1.id <> (
    	SELECT MAX(t2.id) FROM t_user AS t2 WHERE t1.user_name=t2.user_name
    )

*  方法三查询出的所有多余的重复记录：

![在这里插入图片描述][e86e18b0060b4073b3e0fbfadcb6596d.png]  
这里方法三因为用了MAX()方法（也可改用MIN()），查询结果记录的id不太一样，但也可以被视为重复多余的数据，关键是你希望选择保留哪一条记录而已。

*  下面是对上面的SELECT语句稍作修改并加入了DELETE

-- 方法一（笨方法但容易理解）
    DELETE FROM t_user WHERE user_name IN (
    	SELECT t1.user_name FROM (
    		-- 查询出所有重复的user_name
    		SELECT user_name FROM t_user GROUP BY user_name HAVING COUNT(1)>1
    	) t1
    ) 
    AND id NOT IN (
    	SELECT t2.min_id FROM (
    		-- 查询出所有重复的记录并各自只取其中一条（MIN(id)或MAX(id)都可以）
    		SELECT MIN(id) AS min_id FROM t_user GROUP BY user_name HAVING COUNT(1)>1
    	) t2
    )

-- 方法二（推荐方法也容易理解）
    DELETE FROM t_user WHERE id NOT IN (
    	SELECT t.min_id FROM (
    		-- 过滤出重复多余的数据，比如，如果所有记录中存在1条记录是user_name=zhangsan的，那么就取出它；
        	-- 如果所有记录中存在多条记录是user_name=lisi的，那么只取其中1条，其他的不查询出来
    		SELECT MIN(id) AS min_id FROM t_user GROUP BY user_name
      ) t
    )

-- 方法三（推荐方法但不太容易理解）
    DELETE FROM t_user WHERE id IN (
    	SELECT t.id FROM (
    		-- 1. 关于所有存在相同user_name的记录，只查询出（保留）重复记录中的1条，假设这样查询出来的集合为A集合。
    		-- 2. 在所有记录中，只要id不在A集合中的，都把它们查询出来
    		SELECT t1.id FROM t_user AS t1 WHERE t1.id <> (SELECT MAX(t2.id) FROM t_user AS t2 WHERE t1.user_name=t2.user_name)
    	) t
    )
    -- 或
    DELETE FROM t_user t1
    WHERE t1.id <> (
    	SELECT t2.max_id FROM (
    		SELECT MAX(t3.id) AS max_id FROM t_user t3 WHERE t1.user_name=t3.user_name
    	) t2
    )

*  最后删除成功之后，显示数据已经没有重复的了

参考：

[如何实现 MySQL 中通过SQL语句删除重复记录并且只保留一条记录\_51CTO博客\_删除重复记录的sql  
][MySQL _SQL_51CTO_sql]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/ea91592f7bfe483ba9c40386419ff958.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/e48b4976a16444c7a0ae6f4ac34128a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/4ef475f4be3b46ef9f627327bd631ad8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzM1NTc3_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/528cf680152f4eceb2d09f8796b833b4.png
[a0d969f383944414ad125bc61b7647aa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/198309c1ecc24d729b46c5c97ff2aaec.png
[d0cf4f1d36744cabb04c76dd5aeec66f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/9050383091764ae3b96b06cbf7b1ce45.png
[5b0a197e312a47d98711975fbc7f4f76.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bcad469b1c4d4fc9a756e420a8bdee5c.png
[e9b5f2db708541508941b8b89fb60f35.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/e8224c59d0f64bb293e890a011f6dae2.png
[e86e18b0060b4073b3e0fbfadcb6596d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/f1537ac471a24ecbace88ed327d1c39a.png
[MySQL _SQL_51CTO_sql]: https://blog.51cto.com/u_15191078/5226852