想法很简单,如下:按分组选出不重复address的id,然后删除id不等于这些唯一id的:
delete from windmachine where id not in(select id from windmachine group by address)
但是mysql执行错误:
Error Code : 1093
You can't specify target table 'windmachine' for update in FROM clause
(0 ms taken)
这个错误是说delete 与 update不能有 select。
但是改成这样就ok了:
delete from windmachine where id not in
( select * from
(select id from windmachine group by address) b
where windmachine.id=b.id
====================
呵呵,是不是很简单。
另外,也可以保留id最大/最小的行(删除其他行):
delete from windmachine where id not in
( select * from
(select max(id) as max_id from windmachine group by address) b
where windmachine.id=b.max_id
)