又遇到 sql表去重，这次记录一下。
第一个sql，先查询重复的数据。
SELECT o.sid, count( 1 ) FROM iot_one o GROUP BY o.sid HAVING count( 1 ) > 1;
第二个sql，查询重复数据中 id最小的一个数据的id值。
SELECT min( o.id ) FROM iot_one o GROUP BY o.sid HAVING count( 1 ) > 1;
第三个sql，进行数据删除。
DELETE FROM iot_one WHERE 1 = 1 AND sid IN ( SELECT o.sid FROM iot_one o GROUP BY o.sid HAVING count( 1 ) > 1 ) AND id NOT IN ( SELECT min( o.id ) FROM iot_one o GROUP BY o.sid HAVING count( 1 ) > 1 );
这个sql执行结果：
delete from iot_one where 1 = 1 and sid in (select o.sid from iot_one o group by o.sid HAVING count(1) > 1) and id not in (select min(o.id) from iot_one o group by o.sid HAVING count(1) > 1) > 1093 - You can‘t specify target table ‘iot_one‘ for update in FROM clause > 时间: 0.028s
“You can‘t specify target table ‘iot_one‘ for update in FROM clause”错误
代表同一表中，查询数据不能作为更新数据。
第三个sql优化：
DELETE FROM iot_one WHERE 1 = 1 AND sid IN ( SELECT * FROM ( SELECT o.sid FROM iot_one o GROUP BY o.sid HAVING count( 1 ) > 1 ) a ) AND id NOT IN ( SELECT * FROM ( SELECT min( o.id ) FROM iot_one o GROUP BY o.sid HAVING count( 1 ) > 1 ) b );
原文：https://www.cnblogs.com/wgy1/p/14752708.html