Oracle查询及删除重复数据

言7637

CREATE TABLE test (col VARCHAR2( 20))
INSERT INTO test VALUES( 'a');
INSERT INTO test VALUES( 'b');
INSERT INTO test VALUES( 'c');
INSERT INTO test VALUES( 'a');
INSERT INTO test VALUES( 'c');
COMMIT;
--删除重复数据
DELETE test t WHERE ROWID <> ( SELECT MIN (ROWID) FROM test t2 WHERE t.col = t2.col);
COMMIT;
SELECT t.*,ROWID FROM test t


CREATE TABLE test (col1 VARCHAR2( 10),col2 VARCHAR2(10))
INSERT INTO test VALUES( '1','a' );
INSERT INTO test VALUES( '1','b' );
INSERT INTO test VALUES( '2','c' );
INSERT INTO test VALUES( '2','c' );
INSERT INTO test VALUES( '2','a' );
--查询重复数据
SELECT * FROM test t WHERE ROWID > (SELECT MIN( ROWID) FROM test t2 WHERE t.col2 = t2.col2 AND t.col1 = t2.col1)