假设有一个用户表 user,数据如下: 1、查询表中 uid 重复的数据 SELECT id, uid, name FROM USER WHERE uid IN (SELECT
假设有一个用户表 user,数据如下:
SELECT
id,
uid,
name
FROM USER
WHERE uid IN (SELECT
uid
FROM USER
GROUP BY uid
HAVING COUNT(uid) > 1);
SELECT
id,
uid,
name
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);
DELETE
FROM USER
WHERE 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);
一开始直接使用以下语句删除,报错:You can’t specify target table ‘user’ for update in FROM clause
DELETE
FROM USER
WHERE 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 USER
WHERE 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);
--结束END--
本文标题: MySQL查询重复数据
本文链接: https://www.lsjlt.com/news/6125.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-28
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0