mysql - 删除重复数据，保留一条

最新推荐文章于 2024-04-30 17:49:31 发布

重生之我是一名程序员

最新推荐文章于 2024-04-30 17:49:31 发布

阅读量60

点赞数

文章标签： mysql

原文链接：https://editor.csdn.net/md/?not_checkout=1&spm=1010.2135.3001.4503

版权

mysql - 删除重复数据，保留一条

# 查询要删除的数据
SELECT
	id 
FROM
	t_user_tag 
WHERE
	id NOT IN (
	SELECT
		a.id 
	FROM
		( SELECT max( id ) id FROM t_user_tag GROUP BY user_id, tag_id HAVING count( * )> 1 ) a 
	) 
	AND ( user_id, tag_id ) IN (
	SELECT
		t.user_id,
		t.tag_id 
	FROM
		( SELECT user_id, tag_id FROM t_user_tag GROUP BY user_id, tag_id HAVING count(*)> 1 ) t 
	);
	
## 删除并保留一条
DELETE 
FROM
	t_user_tag 
WHERE
	id NOT IN (
	SELECT
		a.id 
	FROM
		( SELECT max( id ) id FROM t_user_tag GROUP BY user_id, tag_id HAVING count( * )> 1 ) a 
	) 
	AND ( user_id, tag_id ) IN (
	SELECT
		t.user_id,
		t.tag_id 
	FROM
	( SELECT user_id, tag_id FROM t_user_tag GROUP BY user_id, tag_id HAVING count(*)> 1 ) t )