在mysql里，删除重复记录（只保留一条）和You can't specify target table for update in FROM clause错误的解决

先创建了users的表

一般我们会用下面方法

DELETE FROM users WHERE id NOT IN
(SELECT MIN(id) FROM users 
GROUP BY username);

但是结果会报以下错误：

<e>查询：delete FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY username)

错误代码： 1093
You can't specify target table 'users' for update in FROM clause

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.001 sec

后来经过网上搜寻原因，最终自己得出了以下删除方法：
第一种：

DELETE FROM users WHERE id NOT IN (
	SELECT t.tid FROM (
	SELECT MIN(id) AS tid FROM users GROUP BY username
	) t
)

第二种：

DELETE FROM users
WHERE username IN(
	SELECT t.username FROM
	(SELECT id,username FROM users
		GROUP BY username
		HAVING COUNT(1)>1
	) t
	) AND id NOT IN 
	(SELECT dt.minid FROM 
		(SELECT MIN(id) AS minid FROM users
			GROUP BY username
			HAVING COUNT(1)>1
		) dt
	);

这样，重复记录就完全删除了:

如果对你有帮助，记得点赞