sql删除重复数据

最新推荐文章于 2024-07-12 15:06:15 发布

转载最新推荐文章于 2024-07-12 15:06:15 发布 · 79 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/leyi/p/10802501.html

文章标签：

#数据库 #爬虫

此博客为转载内容，原链接为https://www.cnblogs.com/leyi/p/10802501.html，原内容涉及数据库和爬虫相关信息技术内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


用爬虫爬了一些数据，但是有些标题是重复的，需要删除，所以找了一下删除重复标题数据的sql。

# 查询所有重复的数据
select * 
FROM
	tb_xici_article 
WHERE
	post_title IN ( SELECT post_title FROM tb_xici_article GROUP BY post_title HAVING count( post_title ) > 1 )
	
	
# 查询所有重复并且id不是最小的那些重复数据
SELECT
	* 
FROM
	tb_xici_article 
WHERE
	post_title IN ( SELECT post_title FROM tb_xici_article GROUP BY post_title HAVING count( post_title ) > 1 ) 
	AND id NOT IN ( SELECT min( id ) FROM tb_xici_article GROUP BY post_title HAVING count( post_title ) > 1 )
	

# 删除重复数据
如果直接按下面这样写，mysql会报You can't specify target table for update in FROM clause错误，需要把select出的结果再通过中间表select一遍
DELETE
FROM
	tb_xici_article 
WHERE
	post_title IN ( SELECT post_title FROM tb_xici_article GROUP BY post_title HAVING count( post_title )
	> 1 ) and id not in (select min(id) from tb_xici_article group by post_title HAVING count(post_title) > 1)
	
#	最终版
DELETE 
FROM
	tb_xici_article 
WHERE
	id IN (
	SELECT
		temp.id 
	FROM
		(
		SELECT
			* 
		FROM
			tb_xici_article 
		WHERE
			post_title IN ( SELECT post_title FROM tb_xici_article GROUP BY post_title HAVING count( post_title ) > 1 ) 
			AND id NOT IN ( SELECT min( id ) FROM tb_xici_article GROUP BY post_title HAVING count( post_title ) > 1 ) 
		) temp 
	)

转载于:https://www.cnblogs.com/leyi/p/10802501.html