MySQL查询重复数据
假设有⼀个⽤户表 user，数据如下：
1、查询表中 uid 重复的数据
SELECT
mysql删除重复的数据保留一条id,
uid,
name
FROM USER
WHERE uid IN (SELECT
uid
FROM USER
GROUP BY uid
HAVING COUNT(uid) >1);
2、查询表中重复数据，排除最⼩id
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);
3、删除表中重复数据，如果是重复数据，则保留id最⼩的⼀条
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);
4、遇到的问题：
⼀开始直接使⽤以下语句删除，报错：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);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。