如何查找重复记录? SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2); 如何删除重复记录? DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
我要删的是两张表的重复,对于单张表自身的重复不用处理。两张表重复的数据,保留tab2的数据。 1) delete from tab2 b WHERE ROWID > ( SELECT min(rowid) FROM tab1 a WHERE a.A1=b.A1); 2) create table tab3 as select A1,A2,A3 from (select a.A1,a.A2,a.A3 from tab1 a union select b.A1,b.A2,b.A3 from tab2 b ) 这样效率是不是很低?
SELECT * FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2); 如何删除重复记录?
DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
SELECT min(rowid) FROM tab1 a
WHERE a.A1=b.A1);
2)
create table tab3
as select A1,A2,A3 from
(select a.A1,a.A2,a.A3 from tab1 a
union
select b.A1,b.A2,b.A3 from tab2 b ) 这样效率是不是很低?