mysql查重去除重复数据

最新推荐文章于 2023-12-29 11:16:05 发布

原创最新推荐文章于 2023-12-29 11:16:05 发布 · 736 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

mysql 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍如何使用SQL查询和删除重复数据，包括根据单列或多列判断重复，以及如何保留ID最小的记录。提供了具体SQL语句示例，帮助读者理解和应用。

1.根据one列查询重复的数据(根据单列判断重复)

SELECT * FROM testdelete 
WHERE ONE IN (SELECT ONE FROM testdelete GROUP BY ONE HAVING COUNT(ONE) > 1)

2.删除表中的重复记录:（根据单列删除且保留ID最小的一条） 错误：不能直接在查询的语句中进行操作

-- 错误语句 错误：不能直接在查询的语句中进行操作
DELETE
FROM testdelete
WHERE ONE IN(SELECT
               ONE
             FROM testdelete
             GROUP BY ONE
             HAVING COUNT(ONE) > 1)
    AND id NOT IN(SELECT
                    MIN(id)
                  FROM testdelete
                  GROUP BY ONE
                  HAVING COUNT(ONE) > 1)

解决办法:将查询包装一层:

DELETE
FROM testdelete
WHERE ONE IN(SELECT
               ONE
             FROM (SELECT
                     ONE
                   FROM testdelete
                   GROUP BY ONE
                   HAVING COUNT(ONE) > 1) a)
    AND id NOT IN(SELECT
                    *
                  FROM (SELECT
                          MIN(id)
                        FROM testdelete
                        GROUP BY ONE
                        HAVING COUNT(ONE) > 1) b)

3.根据one,two,three判断重复:(根据单多判断重复)

SELECT * FROM testdelete a 
WHERE (a.one,a.two,a.three) IN (SELECT ONE,two,three FROM testdelete GROUP BY ONE,two,three HAVING COUNT(*) > 1)

4.删除表中的重复数据(根据多列进行删除且保留ID最小的一条)

DELETE
FROM testdelete
WHERE (ONE,two,three)IN(SELECT
                          ONE,
                          two,
                          three
                        FROM (SELECT
                                ONE,
                                two,
                                three
                              FROM testdelete
                              GROUP BY ONE,two,three
                              HAVING COUNT( * ) > 1) a)
    AND id NOT IN(SELECT
                    MIN(id)
                  FROM (SELECT
                          MIN(id) AS id
                        FROM testdelete
                        GROUP BY ONE,two,three
                        HAVING COUNT( * ) > 1) b)

5. 查找表中多余的重复记录（多个字段），不包含id最小的记录 (根据多个字段查重复不包含id最小的)

SELECT *
FROM testdelete a
WHERE (a.one,a.two,a.three)IN(SELECT
                                ONE,
                                two,
                                three
                              FROM testdelete
                              GROUP BY ONE,two,three
                              HAVING COUNT( * ) > 1)
    AND id NOT IN(SELECT
                    MIN(id) AS id
                  FROM testdelete
                  GROUP BY ONE,two,three
                  HAVING COUNT( * ) > 1)