网上查了下资料,很多都是这种
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)