创建了一个表,其中记录的重复类似如下:a1,b1,valuea, b, 1
a, b, 2...e, f, 1
e, f, 2...
现想删除重复前两列重复的记录,删除时要删掉第三列比较小的记录,也就是说,
删除
a, b, 1
...
e, f, 1我采用了如下删除方法,但是删除的是,a,b,2及e,f,2mQuery.exec("create table receiver_infotmp like receiver_info");
mQuery.exec("insert into receiver_infotmp select distinct * from receiver_info group by a1,b1 order by value asc"));mQuery.exec("drop table receiver_info");
mQuery.exec("rename table receiver_infotmp to receiver_info");
a, b, 2...e, f, 1
e, f, 2...
现想删除重复前两列重复的记录,删除时要删掉第三列比较小的记录,也就是说,
删除
a, b, 1
...
e, f, 1我采用了如下删除方法,但是删除的是,a,b,2及e,f,2mQuery.exec("create table receiver_infotmp like receiver_info");
mQuery.exec("insert into receiver_infotmp select distinct * from receiver_info group by a1,b1 order by value asc"));mQuery.exec("drop table receiver_info");
mQuery.exec("rename table receiver_infotmp to receiver_info");
DELETE A FROM TT A INNER JOIN
(SELECT A1,B1,MIN(VALUE) AS MI FROM TT GROUP BY A1,B1) B
ON A.A1=B.A1 AND A.B1=B.B1 AND A.VALUE=B.MI
on a.a1=b.a1 and a.b1=b.b1 and a.value=b.value
where b.a1 is null
是mysql,不过,我对sql语言了解不深,A代表什么,TT是什么,