MySQL查询重复数据

  • Z时代
  • 2024-01-10
  • 分类：综合

database

 

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

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

SELECT
  id,
  uid,
  name
FROMUSER
WHERE uid IN (SELECT
    uid
FROMUSER
GROUPBY uid
HAVINGCOUNT(uid) >1);

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

SELECT
  id,
  uid,
  name
FROMuser
WHERE uid IN (SELECT
    uid
FROMuser
GROUPBY uid
HAVINGCOUNT(uid) >1)
AND id NOTIN (SELECT
MIN(id)
FROMuser
GROUPBY uid
HAVINGCOUNT(uid) >1);

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

DELETE
FROMUSER
WHERE id IN (SELECT
      u.id
FROM (SELECT
        id
FROMUSER
WHERE uid IN (SELECT
          uid
FROMUSER
GROUPBY uid
HAVINGCOUNT(uid) >1)
AND id NOTIN (SELECT
MIN(id)
FROMUSER
GROUPBY uid
HAVINGCOUNT(uid) >1)) AS u);

4、遇到的问题：

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

 

DELETE
FROMUSER
WHERE id IN (SELECT
      id
FROMUSER
WHERE uid IN (SELECT
        uid
FROMUSER
GROUPBY uid
HAVINGCOUNT(uid) >1)
AND id NOTIN (SELECT
MIN(id)
FROMUSER
GROUPBY uid
HAVINGCOUNT(uid) >1));

 

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

 

DELETE
FROMUSER
WHERE id IN (SELECT
      u.id
FROM (SELECT
        id
FROMUSER
WHERE uid IN (SELECT
          uid
FROMUSER
GROUPBY uid
HAVINGCOUNT(uid) >1)
AND id NOTIN (SELECT
MIN(id)
FROMUSER
GROUPBY uid
HAVINGCOUNT(uid) >1)) AS u);

 

以上是 MySQL查询重复数据 的全部内容， 来源链接： utcz.com/z/533664.html

回到顶部