mysql 删除重复数据，只保留最新一条

最新推荐文章于 2024-08-07 14:51:18 发布

weixin_33794672

最新推荐文章于 2024-08-07 14:51:18 发布

阅读量226

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/436053/blog/1358476

版权

2019独角兽企业重金招聘Python工程师标准>>>

正确操作：

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)

转载于:https://my.oschina.net/u/436053/blog/1358476