mysql重复行数删除_mysql 删除重复行数据

最新推荐文章于 2024-07-30 08:00:00 发布

范沙子

最新推荐文章于 2024-07-30 08:00:00 发布

阅读量272

点赞数

文章标签： mysql重复行数删除

本文链接：https://blog.csdn.net/weixin_33196106/article/details/113205637

版权

网上查了下资料，很多都是这种

delete from people

where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)

and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)

这种在mysql里是不支持的，会报“You can't specify target table 'people' for update in FROM clause” 的错误(应该是由于mysql在删除的时候加了锁，具体是行锁还是别的锁暂时没有查清楚)。所以修改了上面的语句，加了临时表就可以了。

delete from people

where peopleName in (select peopleName from (select peopleName from people group by peopleName having count(peopleName) > 1) a)

and peopleId not in (select peopleId from (select min(peopleId) peopleId from people group by peopleName having count(peopleName)>1) b)

上面这个虽然可以实现，但是还可以优化，一般peopleId为索引或者为主键，所以先用这个来过滤一部分数据(如果是大表的话，这个非常关键)，然后再用另一个条件回表搜索。

delete from people

where peopleId not in (select peopleId from (select min(peopleId) peopleId from people group by peopleName having count(peopleName)>1) b)

and peopleName in (select peopleName from (select peopleName from people group by peopleName having count(peopleName) > 1) a)

关注