mysql去重

又遇到 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 
    );