数据如下
目的:去重name值并且保留ID最大的记录
//查询 查看是否是需要删除的记录
SELECT *
FROM test where id not in (
SELECT id from (SELECT MAX(id) id FROM test GROUP BY name) a
);
//删除对应的记录
DELETE
FROM test where id not in (
SELECT id from (SELECT MAX(id) id FROM test GROUP BY name) a
);
如果mysql大于5.7版本
可能会报错
SELECT id from (SELECT name,id FROM test GROUP BY name HAVING count(1)>1) a
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.test.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
需要修改my.ini 去掉sql_model 中 only_full_group_by