oracle__ Delete duplicate records __ Three Ways and summary (reprint Baidu Library)

Http://wenku.baidu.com/link?url=RIENeGUK4sjxe21_RBYLYHR9tbUUCmOZQRR0mIjldXLYwRAt4khDtLQD9dFyd3rz3s_ Hwlvg2oertw8sjub1r2qlqqszabo3xla8tu2qd9q

--Method 1:rowid

--Show Duplicate row select * from persons p1 where rowid<> (select Max (ROWID) from persons p2 where p1.pid=p2.pid);

Description

If you have more than one field, add it, such as: SELECT * from persons p1 where rowid<> (select Max (ROWID) from persons P2 where p1.pid=p2.pid an D p1.pname=p2.pname);

--delete duplicate row delete from persons P1 where rowid<> (select Max (ROWID) from persons p2 where p1.pid=p2.pid);

Summarize:

A: The records of each table may be the same, but the rowid of the records of each table is unique;

B: The idea of doing a problem

A: Display the maximum rowID b for the same field: Leave the record with the largest rowid of the same field, and delete the other records;

-Method Two: Group by

According to the PID group, show the number of PID more than 1 records

Select PID from Persons GROUP by PID have Count (PID) >1;

Delete duplicate rows

Delete from persons where the PID in (the Select PID from the persons Group by PID has a count (PID) >1);

This method deletes all rows with duplicate records and does not leave a burn;

--Method Three: Distinct

Cancel duplicate Row SELECT DISTINCT * from persons;

CREATE TABLE and copy "data from table with duplicate rows" created table table_new as SELECT DISTINCT * from persons;

Delete data from persons table delete from persons;

Insert data that does not have duplicate rows in the Table_new table into the persons table insert into persons select * from Table_new;

Summarize:

Use the intermediate table to import and export data to delete duplicate rows;