mysql表排重

表1  test

在表test中name为bb的记录有重复，在这里需要删除重复记录。

查重语句：SELECT * from test GROUP BY name HAVING count(1)>1 ORDER BY name；

有人可能会想到：由上述语句可写删除重复语句为：delete from test where name in (SELECT * from test GROUP BY name HAVING count(1)>1 ORDER BY name)；

由于目前mysql不支持这样的语句，报错，排重可以使用建立临时表的方法：

create table tmp as select min(id) as col1 from test group by name;

delete from test where id not in (select col1 from tmp); 
drop table tmp;