MySQL删除表中重复数据SQL

最新推荐文章于 2024-07-30 08:00:00 发布

double_q_y

最新推荐文章于 2024-07-30 08:00:00 发布

阅读量497

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/dianyou8752/article/details/100544598

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

工作中遇到MySQL对应系统表中存在脏数据的问题，重复的数据也属于脏数据的一种。

如何去除表中重复数据，正确SQL如下：

DELETE
FROM
	pg_invoice_info_copy
WHERE
	(
		pg_invoice_code,
		pg_invoice_number,
		pg_invoice_amount,
		pg_invoice_tax_amount
	) IN (
		SELECT
			pg_invoice_code,
			pg_invoice_number,
			pg_invoice_amount,
			pg_invoice_tax_amount
		FROM
			(
				SELECT
					pg_invoice_code,
					pg_invoice_number,
					pg_invoice_amount,
					pg_invoice_tax_amount
				FROM
					pg_invoice_info_copy
				GROUP BY
					pg_invoice_code,
					pg_invoice_number,
					pg_invoice_amount,
					pg_invoice_tax_amount
				HAVING
					count(*) > 1
			) f
	)
AND id NOT IN (
	SELECT
		id
	FROM
		(
			SELECT
				min(id) AS id
			FROM
				pg_invoice_info_copy
			GROUP BY
				pg_invoice_code,
				pg_invoice_number,
				pg_invoice_amount,
				pg_invoice_tax_amount
			HAVING
				count(*) > 1
		) t
)

执行结果如下：
在这里插入图片描述
这里标记一下自己踩过坑的地方：