正确操作:
DELETE FROM pay_vip_point
WHERE id NOT IN
(SELECT nd.i FROM
(SELECT MAX(id) i FROM pay_vip_point GROUP BY user_id, community_code HAVING COUNT(*) > 1
UNION
SELECT MAX(id) i FROM pay_vip_point GROUP BY user_id, community_code HAVING COUNT(*) = 1) nd
)
中间的临时表不能省,mysql会报错:You can't specify target table 'pay_vip_point' for update in FROM clause
下面是错误示例:
DELETE FROM pay_vip_point
WHERE id NOT IN
(SELECT MAX(id) FROM pay_vip_point GROUP BY user_id, community_code HAVING COUNT(*) > 1
UNION
SELECT MAX(id) FROM pay_vip_point GROUP BY user_id, community_code HAVING COUNT(*) = 1)