执行如下sql语句,出现 You can't specify target table 'tb_test' for update in FROM clause 错误
,这个错误的意思是
不能在同一个sql语句中,先select同一个表的某些值,然后再delete这个表中的数据
解决方法:select的结果再通过一个中间表select多一次,就可以避免这个错误
改进后的sql:
DELETE FROM tb_test
WHERE username IN (SELECT a.`username`
FROM (SELECT username
FROM tb_test GROUP BY username HAVING COUNT (username) > 1) a )
AND id NOT IN (SELECT b.min_id
FROM (SELECT MIN(id) min_id
FROM tb_test GROUP BY username HAVING COUNT (username) > 1) b )
执行后结果为: