重复记录的处理方法

1.rowid——记录所在的磁盘位置，可以实现快速定位

SELECT * FROM t1 WHERE ID=? 慢于 SELECT * FROM t1 WHERE ROWID=?

 

DELETE FROM t1 a WHERE ROWID !=  (SELECT MAX(ROWID) FROM t1 b WHERE a.f1 = b.f1 AND a.f2 = b.f2)——不能用于大表，如1000W的表

 

 

2.临时表

CREATE TABLE t1_temp
AS
SELECT DISTINCT f1,f2 FROM t1;

DELETE FROM t1;
INSERT INTO t1 SELECT * FROM t1_temp;

SELECT * FROM t1;

 

3.唯一的制动编号

 

AUTOID F1 F2
1  1 1
2  1 1
3  1 1
4  1 2
5  2 1
6  2 2
7  2 2
8  1 1
9  1 1

DELETE FROM t1 a WHERE autoid<(
   SELECT MAX(autoid) FROM t1 b WHERE a.f1=b.f1
);