(转贴) 在Oracle中删除重复的记录四种方法- - 表T结构如下: COL1 COL2 COL3 中有重复记录(COL1,COL2为主键),如何删除1、有少数重复记录(在col1,col2上有索引比较好) DELETE T WHERE (COL1,COL2) IN (SELECT COL1,COL2 FROM T GROUP BY COL1,COL2 HAVING COUNT(*) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2 HAVING COUNT(*) > 1)2、大部份记录有重复记录 DELETE T WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2)3、其他写法 DELETE T WHERE ROWID IN (SELECT A.ROWID FROM T A,T B WHERE A.COL1=B.COL1 AND A.COL2 = B.COL2 AND A.ROWID > B.ROWID)4.delete from T a where a.rowid!=(select max(rowid) from T b where a.col1 =b.col1 and a.col2=b.col2);
我個人認爲,不論重復的多少用下面的語句應該都挺好 DELETE T WHERE ROWID in (SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2 having count(*)>1)
在Oracle中删除重复的记录四种方法- - 表T结构如下:
COL1
COL2
COL3
中有重复记录(COL1,COL2为主键),如何删除1、有少数重复记录(在col1,col2上有索引比较好)
DELETE T
WHERE (COL1,COL2) IN
(SELECT COL1,COL2 FROM T GROUP BY COL1,COL2 HAVING COUNT(*) > 1)
AND
ROWID NOT IN
(SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2 HAVING COUNT(*) > 1)2、大部份记录有重复记录
DELETE T WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2)3、其他写法
DELETE T WHERE ROWID IN
(SELECT A.ROWID FROM T A,T B
WHERE A.COL1=B.COL1 AND A.COL2 = B.COL2 AND A.ROWID > B.ROWID)4.delete
from T a
where a.rowid!=(select max(rowid) from T b where a.col1 =b.col1 and a.col2=b.col2);
DELETE T WHERE ROWID in
(SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2 having count(*)>1)