MySQL查询重复数据

假设有一个用户表 user，数据如下：

1、查询表中 uid 重复的数据

SELECT  id,  uid,  nameFROM USERWHERE uid IN (SELECT    uid  FROM USER  GROUP BY uid  HAVING COUNT(uid) > 1);

2、查询表中重复数据，排除最小id

SELECT  id,  uid,  nameFROM userWHERE uid IN (SELECT    uid  FROM user  GROUP BY uid  HAVING COUNT(uid) > 1)AND id NOT IN (SELECT    MIN(id)  FROM user  GROUP BY uid  HAVING COUNT(uid) > 1);

3、删除表中重复数据，如果是重复数据，则保留id最小的一条

DELETE  FROM USERWHERE id IN (SELECT      u.id    FROM (SELECT        id      FROM USER      WHERE uid IN (SELECT          uid        FROM USER        GROUP BY uid        HAVING COUNT(uid) > 1)      AND id NOT IN (SELECT          MIN(id)        FROM USER        GROUP BY uid        HAVING COUNT(uid) > 1)) AS u);

4、遇到的问题：

一开始直接使用以下语句删除，报错：You can’t specify target table ‘user’ for update in FROM clause

DELETE  FROM USERWHERE id IN (SELECT      id    FROM USER    WHERE uid IN (SELECT        uid      FROM USER      GROUP BY uid      HAVING COUNT(uid) > 1)    AND id NOT IN (SELECT        MIN(id)      FROM USER      GROUP BY uid      HAVING COUNT(uid) > 1));

查资料后得知：
因为在 MYSQL 里，不能先 select 一个表的记录，在按此条件进行更新和删除同一个表的记录。
解决办法：
将 select 得到的结果，再通过中间表 select 一遍。
SQL如下：

DELETE  FROM USERWHERE id IN (SELECT      u.id    FROM (SELECT        id      FROM USER      WHERE uid IN (SELECT          uid        FROM USER        GROUP BY uid        HAVING COUNT(uid) > 1)      AND id NOT IN (SELECT          MIN(id)        FROM USER        GROUP BY uid        HAVING COUNT(uid) > 1)) AS u);

文章转载于:https://www.cnblogs.com/woods1815/p/12901717.html

原著是一个有趣的人,若有侵权,请通知删除

本博客所有文章如无特别注明均为原创。
复制或转载请以超链接形式注明转自起风了，原文地址《MySQL查询重复数据》