Oracle Query duplicate data and delete duplicate record method

For example, there is now a table (table name: peosons) drop table persons;create table PERSONS (PNAME VARCHAR2 (), CARDID VARCHAR2 (+), ADDRESS Varc HAR2); INSERT into persons (PNAME, CARDID, ADDRESS) VALUES (' Zhang San ', ' 430682199002121010 ', ' Shenzhen '); INSERT into persons (PNAME, CARDID, address) VALUES (' John Doe ', ' 430682199002121010 ', ' Shenzhen '); INSERT into persons (PNAME, CARDID, address) VALUES ( ' Harry ', ' 430682199002121010 ', ' Shenzhen '); INSERT into persons (PNAME, CARDID, ADDRESS) VALUES (' Zhang San ', ' 430682199002121010 ', ' Shenzhen '); Insert into persons (PNAME, CARDID, ADDRESS) VALUES (' Zhao Liu ', ' 430682199002121011 ', ' Shenzhen '); INSERT into persons (PNAME, Cardi D, ADDRESS) VALUES (' Zhao Liu ', ' 430682199002121011 ', ' Shenzhen '); INSERT into persons (PNAME, CARDID, address) values (' Little plums ', ' 430682 199002121011 ', ' Shenzhen '); INSERT into persons (PNAME, CARDID, ADDRESS) VALUES (' Little plum ', ' 430682199002121012 ', ' Shenzhen '); INSERT into Persons (PNAME, CARDID, address) VALUES (' Small Zhang Zihe ', ' 430682199002121013 ', ' Shenzhen '); INSERT into persons (PNAME, CARDID, address) Values (' Little Zhang Zihe ', ' 430682199002121013 ', ' Shenzhen '); If you want to query the exact records of the three fields, such as name, ID number and address, select p1.* from Persons P1, persons P2 where P1.rowid <> p 2.rowid and P1.cardid = P2.cardid and P1.pname = p2.pname and p1.address = p2.address; Sql> Select p1.* 2 from Persons P1, persons P2 3 where P1.rowid <> p2.rowid 4 and P1.cardid = P2.card ID 5 and P1.pname = P2.pname 6 and p1.address = p2.address;                 PNAME CARDID ADDRESS----------------------------------------------------------Zhang San                 430682199002121010 Shenzhen Zhang 3.,430,682,199,002,12e,+18 Shenzhen Zhao 6.,430,682,199,002,12e,+18 shenzhen Zhao Liu 430682199002121011 Shenzhen small Zhang Zihe 430682199002121013 shenzhen small Zhang Zihe 430682199002121013 shenzhen has selected 6 lines.　　Can achieve the above effect. Several SQL statements that delete duplicate records 1. Using the rowID Method 2. Using the Group by Method 3. Using the Distinct method 1. Using the rowID method according to the ROWID attribute of the Oracle band, to determine whether there are duplicates, the statement is as follows: SELECT * FROM          Persons a WHERE ROWID! = (select Max (ROWID) from persons b where a.pname = B.pname          and A.cardid = B.cardid and a.address = b.address); Sql> SELECT * 2 from Persons a 3 where ROWID! = (select Max (ROWID) from persons B 4 where A.pname = B.pname 5 and A.cardid = B.cardid 6 and a.address = b.address);                 PNAME CARDID ADDRESS----------------------------------------------------------Zhang San 430682199002121010 Shenzhen Zhao 6.,430,682,199,002,12e,+18 Shenzhen small Zhang Zihe 430682199002121013 shenzhen Delete duplicate data, retain ROWID Max D                    Elete from persons a WHERE ROWID! = (select Max (ROWID) from persons b where a.pname = B.pname and A.cardid = B.cardid and a.address = b.address); 2.group by Method Select COUNT (pname), Max (pname ) from persons--lists the number of duplicate records and lists his name attribute group by pname----grouped by Panme to find duplicates of pname columns in the table, that is, the number of occurrences is greater than once having count (*) > 1sql> SE  Lect count (pname), Max (pname) 2 from Persons 3 GROUP by pname4 Having COUNT (*) > 1;           COUNT (PNAME) MAX (PNAME)--------------------------------------------------------------2 Zhao 62 small Zhang Zihe 2 Little plums 2 photos Delete data delete from persons where PName in (select PName from Persons group by PName have CO UNT (*) > 1);

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Query duplicate data and delete duplicate record method