如果是要把有重复数据的列都删除掉的话,用下面这个就行了 如果是想留下唯一一条的话要再改改了~delete Table_Name where Col in( select Col from( select Col,count(*) from Table_Name group by Col having count(*)>1 --查出有重复数据的列 ) )
删除重复记录 最高效的删除重复记录方法 ( 因为使用了ROWID) DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
转载: DELETE FROM t WHERE ROWID IN ( SELECT ROWID FROM (SELECT ROWID,ROW_NUMBER () OVER (PARTITION BY A ORDER BY ROWID) rn FROM a) WHERE rn > 1 )
谢谢楼上各位仁兄拉,谢谢能给予帮助,但我试了用了的,和DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 这条语句几乎一样,但由于我所操作的表记录有60多万,象上面这条语句,在执行时它会遍历60万乘60万次,这样执行一天也执行不完。 所以这种方式可能不行,得改改
用临时表试试 CREATE TABLE T1 AS SELECT A, B, C... FROM T GROUP BY A, B, C...; TRUNCATE TABLE T; INSERT INTO T SELECT * FROM T1; DROP TABLE T1;
如果是想留下唯一一条的话要再改改了~delete Table_Name where Col in(
select Col from(
select Col,count(*) from Table_Name group by Col having count(*)>1 --查出有重复数据的列
)
)
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
DELETE FROM t
WHERE ROWID IN
(
SELECT ROWID
FROM (SELECT ROWID,ROW_NUMBER () OVER (PARTITION BY A ORDER BY ROWID) rn
FROM a)
WHERE rn > 1
)
这条语句几乎一样,但由于我所操作的表记录有60多万,象上面这条语句,在执行时它会遍历60万乘60万次,这样执行一天也执行不完。
所以这种方式可能不行,得改改
CREATE TABLE T1 AS SELECT A, B, C... FROM T GROUP BY A, B, C...;
TRUNCATE TABLE T;
INSERT INTO T SELECT * FROM T1;
DROP TABLE T1;