原sql
DELETE from t_food_material where id in (select max(id) from t_food_material group by food_material_name having count(*)>1) and two_level_classify_name='成品食物' order by id
执行结果:
解决方案:
将SELECT查询的结果,通过中间表再次SELECT一遍,这样就规避了错误。改造后的sql如下:
DELETE from t_food_material where id in (select a.id FROM (select max(id) as id from t_food_material group by food_material_name having count(*)>1) a) and two_level_classify_name='xxx'