Oracle query deletes duplicate records three ways _oracle

For example, now there is a person table (table name: peosons)
If you want to have the name, ID number, address of these three fields exactly the same records query

Copy Code code as follows:

Select p1.*
From persons p1,persons P2
where p1.id<>p2.id
and P1.cardid = P2.cardid and P1.pname = p2.pname and p1.address = p2.address

can achieve this 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 rowID method

According to the Oracle band ROWID attribute, to determine whether there are duplicates, the statement is as follows:

Check the data:

Copy Code code as follows:

SELECT * FROM table1 a where rowID!= (select Max (ROWID)
From table1 b where a.name1=b.name1 and a.name2=b.name2 ...)

Delete data:

Copy Code code as follows:

Delete from table1 a where rowID!= (select Max (ROWID)
From table1 b where a.name1=b.name1 and a.name2=b.name2 ...)

2.group by method

Check the data:

Copy Code code as follows:

Select COUNT (num), max (name) from student--Lists the number of duplicate records and lists his Name property
GROUP BY Num
Having count (num) >1--After grouping by NUM, find out that the NUM column in the table repeats, which occurs more than once

Delete data:

Copy Code code as follows:

Delete from student
GROUP BY Num
Having count (num) >1

In this case, all the duplicates are deleted.

3. Using the distinct method-useful for small tables

Copy Code code as follows:

CREATE TABLE Table_new as SELECT DISTINCT * FROM table1 Minux
TRUNCATE TABLE table1;
INSERT INTO table1 select * from Table_new;

How to query and delete duplicate records

1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to judge

Copy Code code as follows:

SELECT * from People
where Peopleid in (select Peopleid from People GROUP by Peopleid has count (Peopleid) > 1)

2, delete redundant records in the table, duplicate records are based on a single field (Peopleid) to judge, leaving only rowid minimal records

Copy Code code as follows:

Delete from people
where Peopleid in (select Peopleid to People GROUP by Peopleid
Having count (Peopleid) > 1)
and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)

3. Find redundant records in the table (multiple fields)

Copy Code code as follows:

SELECT * FROM Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)

4, delete redundant records in the table (multiple fields), leaving only the smallest ROWID records

Copy Code code as follows:

Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

5, look for redundant records in the table (multiple fields), does not contain the smallest ROWID records

Copy Code code as follows:

SELECT * FROM Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

Two

For example, there is a field "name" in Table A, and the "name" value may be the same between different records, and now is the time to query for duplicates of the "name" value between the records in the table.

Copy Code code as follows:

Select Name,count (*) from-A Group by Name has Count (*) > 1

If the same gender is also the same large as the following:

Copy Code code as follows:

Select Name,sex,count (*) from-A Group by Name,sex have Count (*) > 1

Three

Method One

Copy Code code as follows:

Declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) >; 1
Open Cur_rows
Fetch cur_rows into @id, @max
While @ @fetch_status =0
Begin
Select @max = @max-1
SET ROWCOUNT @max
Delete from table name where main field = @id
Fetch cur_rows into @id, @max
End
Close Cur_rows
SET ROWCOUNT 0

Method Two

"Duplicate record" has two duplicate records, one is a completely duplicate record, that is, all the fields are duplicate records, the second is some key fields duplicate records,
For example, the Name field repeats, and the other fields are not necessarily repeated or repeated to be ignored.

1, for the first repetition, easier to solve, the use of

Copy Code code as follows:

SELECT DISTINCT * FROM tablename

You can get a result set without duplicate records.

If the table needs to delete duplicate records (1 of duplicate records are retained), you can delete them in the following ways

Copy Code code as follows:

SELECT DISTINCT * into #Tmp tablename
DROP TABLE TableName
SELECT * INTO TableName from #Tmp
drop table #Tmp

This duplication occurs because the table is poorly designed and can be resolved by adding a unique index column.

2. This type of repetition usually requires the first record in the duplicate record to be retained, as follows

Suppose there is a duplicate field of name,address that requires a unique result set for both fields

Copy Code code as follows:

Select Identity (int,1,1) as Autoid, * into #Tmp from TableName
Select min (autoid) as autoid into #Tmp2 from #Tmp Group by name,autoid
SELECT * from #Tmp where autoid in (select Autoid from #tmp2)

The last select gets the name,address result set (but one more autoid field, which can be written in the SELECT clause to omit this column)

Four

Query duplication

Copy Code code as follows:

SELECT * FROM tablename where ID in (
Select ID from tablename
GROUP BY ID
Having count (ID) > 1
)