删的慢 用替换表create table tmp
select * from tb A
where not exits (select 1 from tb A.TransportID=TransportID and A.TransportName=TransportName and A.autoid < autoid )drop table tb;
alter table tmp rename tb;
select * from tb A
where not exits (select 1 from tb A.TransportID=TransportID and A.TransportName=TransportName and A.autoid < autoid )drop table tb;
alter table tmp rename tb;
例如
autoid TransportID TransportName
1 001 例子
2 001 例子
3 002 例子2
现在就想删除 TransportID 和 TransportName 字段相同的数据 , 但要保留一条
(select TransportID,TransportName,min(id) as aa from tt group by TransportID,TransportName) b
on a.TransportID=b.TransportID and a.TransportName=b.TransportName
and a.id=b.aa
delete a from tt a inner join
(select TransportID,TransportName,min(autoid) as aa from tt group by TransportID,TransportName) b
on a.TransportID=b.TransportID and a.TransportName=b.TransportName
and a.autoid>b.aa