错误代码:
DELETE
FROM
payment
WHERE
serial IN ( SELECT serial FROM payment GROUP BY serial HAVING count(*) > 1 )
AND id NOT IN ( SELECT min( id ) AS id FROM payment GROUP BY serial HAVING count( serial ) > 1 );
错误提示:
正确代码: 使用临时表
DELETE
FROM
payment
WHERE
serial IN ( select serial from (SELECT serial FROM payment GROUP BY serial HAVING count(*) > 1) a )
AND id NOT IN ( select id from (SELECT min( id ) as id FROM payment GROUP BY serial HAVING count( serial ) > 1)b );
效果:
实验前数据
执行sql: