欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL 删除重复数据,重复数据只保留ID最小的行

程序员文章站 2022-06-02 16:01:45
...

 删除重复数据,重复数据只保留ID最小的行

DELETE
FROM
	t_customer_comment
WHERE
	id IN (
		SELECT
			*
		FROM
			(
				SELECT
					id
				FROM
					t_customer_comment
				WHERE
					remaintain_order_id IN (
						SELECT
							remaintain_order_id
						FROM
							t_customer_comment
						GROUP BY
							remaintain_order_id
						HAVING
							count(remaintain_order_id) > 1
						ORDER BY
							count(remaintain_order_id) DESC
					)
				AND id NOT IN (
					SELECT
						min(id)
					FROM
						t_customer_comment t
					GROUP BY
						remaintain_order_id
					HAVING
						count(remaintain_order_id) > 1
					ORDER BY
						min(id) ASC
				)
			) AS ttt
	)

思路:

1:使用 group by ... having 查找出重复的记录 a

2:使用select min(id) group by ...having count(...)>1找出重复数据中id最小的记录 b

3:找出 in a and not in b的数据行,即为需要删除的数据c;

4:直接使用delete from c 在非ORACEL下会报错:[Err] 1093 - You can't specify target table 't_customer_comment' for update in FROM clause;此时需要在c的外边套一层 select * from c as d

5:然后 delete from d ;删除成功!

相关标签: SQL删除重复数据

上一篇: 第二次

下一篇: JS去除重复数据