MySql根据字段名查询重复记录并删除！只保留一条

迷南。 2023-09-28 16:38 26阅读 0赞

> 最近在处理业务数据的时候！在几W条记录里存在着些相同的记录,如何用SQL语句,删除掉重复的呢?
> 
> 可以用以下方法进行处理
> 
> 其实很简单！就是查找表中多余的重复记录，重复记录是根据单个字段来查询、然后删除其他重复的记录即可

## 查询重复记录 ##

SELECT  id ,title from  topic GROUP BY title HAVING count(*) > 1;

![d5b2091b5177437bb597f547c1d02143.png][]

**这样查出来的结果比较乱**

## 查询全部的重复记录并分组 ##

SELECT
    	* 
    FROM
    	topic 
    WHERE
    	title IN ( SELECT title FROM topic GROUP BY  title HAVING Count(*)> 1 ) 
    	AND title IN (
    	SELECT
    		title 
    	FROM
    		topic 
    	GROUP BY
    		title
    HAVING
    	count(*)> 1) ORDER BY title

**对查询结果进行分组根据需要的字段**

![a35c4d1ebea14f95abf1118153efac35.png][]

## 查询除第一条外的多余数据 ##

SELECT
    	*
    FROM
    	topic
    WHERE
    	id NOT IN (
    		SELECT
    			dt.minId
    		FROM
    			(
    				SELECT
    					MIN(id) AS minId
    				FROM
    					topic
    				GROUP BY
    				title
    			) dt
    	)
    ORDER BY
    	title

## 删除第一条外的多余数据 ##

DELETE
    FROM
    	topic
    WHERE
    	id NOT IN (
    		SELECT
    			dt.minId
    		FROM
    			(
    				SELECT
    					MIN(id) AS minId
    				FROM
    					topic
    				GROUP BY
    					title
    			) dt
    	)
    ORDER BY
    	title

[d5b2091b5177437bb597f547c1d02143.png]: https://img-blog.csdnimg.cn/d5b2091b5177437bb597f547c1d02143.png
[a35c4d1ebea14f95abf1118153efac35.png]: https://img-blog.csdnimg.cn/a35c4d1ebea14f95abf1118153efac35.png