比如,表:event(id int(10) auto_increment primary key,
sid int(10)not null,
detail text)
我想删除表event中sid重复的记录,请问有没有这样SQL语句?或是通过其它方法?
复制代码 代码如下:
delete from event as e
where id != (select min(id) from event where sid=e.sid);
where id != (select min(id) from event where sid=e.sid);
or
复制代码 代码如下:
delete from event
where sid not in (select mid from (select sid ,min(id) as mid from event group by sid))
where sid not in (select mid from (select sid ,min(id) as mid from event group by sid))
应该是有用的代码
复制代码 代码如下:
alter ignore table event add unique index idu_sid (sid);
alter table event drop index idu_sid;
alter table event drop index idu_sid;