mysql去除重复数据

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

to_real

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

阅读量1.1w

点赞数 2

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/to_real/article/details/124276968

版权

MySQL 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

这篇博客详细介绍了如何在MySQL中通过SQL查询找出并删除重复的数据。首先，根据单列查询并删除重复记录，保留每个唯一值的最小ID。接着，扩展到多列判断重复，并同样删除保留最小ID的记录。最后，提供了查询不含最小ID重复记录的方法。这些技巧对于数据库管理和优化至关重要。

摘要由CSDN通过智能技术生成

mysql去除重复数据
根据one列查询重复的数据(根据单列判断重复)
SELECT * FROM tab_test
WHERE ONE IN (SELECT ONE FROM testdelete GROUP BY ONE HAVING COUNT(ONE) > 1)
删除表中的重复记录:（根据单列删除且保留ID最小的一条）
DELETE
FROM tab_test
WHERE ONE IN(SELECT
ONE
FROM (SELECT
ONE
FROM tab_test
GROUP BY ONE
HAVING COUNT(ONE) > 1) a)
AND id NOT IN(SELECT
*
FROM (SELECT
MIN(id)
FROM tab_test
GROUP BY ONE
HAVING COUNT(ONE) > 1) b);

根据one,two,three判断重复:(根据单多判断重复)
SELECT * FROM tab_test a
WHERE (a.one,a.two,a.three) IN
(SELECT ONE,two,three FROM tab_test GROUP BY ONE,two,three HAVING COUNT(*) > 1) ;

删除表中的重复数据(根据多列进行删除且保留ID最小的一条)
DELETE
FROM tab_test
WHERE (ONE,two,three)IN(SELECT
ONE,
two,
three
FROM (SELECT
ONE,
two,
three
FROM tab_test
GROUP BY ONE,two,three
HAVING COUNT( * ) > 1) a)
AND id NOT IN(SELECT
MIN(id)
FROM (SELECT
MIN(id) AS id
FROM tab_test
GROUP BY ONE,two,three
HAVING COUNT( * ) > 1) b)

查找表中多余的重复记录（多个字段），不包含id最小的记录 (根据多个字段查重复不包含id最小的)
SELECT *
FROM tab_test a
WHERE (a.one,a.two,a.three)IN(SELECT
ONE,
two,
three
FROM tab_test
GROUP BY ONE,two,three
HAVING COUNT( * ) > 1)
AND id NOT IN(SELECT
MIN(id) AS id
FROM tab_test
GROUP BY ONE,two,three
HAVING COUNT( * ) > 1) ;