创建了一个表,其中记录的重复类似如下: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");
a1 VARCHAR(10),
b1 VARCHAR(10),
c1 INT);
INSERT INTO t_test VALUES
('a','b',1),
('a','b',2),
('e','f',1),
('e','f',2);CREATE TABLE t_test1
AS
SELECT a1,b1,MAX(c1) c1 FROM t_test GROUP BY a1,b1;
DROP TABLE t_test;
RENAME TABLE t_test1 TO t_test;
SELECT * FROM t_test;
a1 b1 c1
a b 2
e f 2
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
DELETE A FROM TT A INNER JOIN tt b on A.A1=B.A1 AND A.B1=B.B1 AND A.VALUE<B.value
on a.a1=b.a1 and a.b1=b.b1 and a.value=b.value
where b.a1 is null