mysql去除重复数据

最新推荐文章于 2024-08-28 08:50:29 发布

热心的码农

最新推荐文章于 2024-08-28 08:50:29 发布

阅读量387

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/chenxianxing/article/details/105933477

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.删除表中的重复记录:（根据单列删除且保留ID最小的一条）

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)

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

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)

3. 查找表中多余的重复记录（多个字段），不包含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)

参考：https://www.cnblogs.com/qlqwjy/p/8270011.html