oracle 保留重复记录,Oracle删除重复记录只保留一条数据的几种方法

1、查找表中多余的重复记录，重复记录是根据单个字段(peopleId)来判断

SELECT

*

FROM

people

WHERE

peopleid IN (

SELECT

peopleid

FROM

people

GROUP BY

peopleid

HAVING

COUNT (peopleid) > 1

)

2、删除表中多余的重复记录，重复记录是根据单个字段(peopleId)来判断，只留有rowid最小的记录

DELETE

FROM

people

WHERE

peopleid IN (

SELECT

peopleid

FROM

people

GROUP BY

peopleid

HAVING

COUNT (peopleid) > 1

)

AND ROWID NOT IN (

SELECT

MIN (ROWID)

FROM

people

GROUP BY

peopleid

HAVING

COUNT (peopleid) > 1

)

3、查找表中多余的重复记录(多个字段)

SELECT

*

FROM

vitae A

WHERE

(A .peopleid, A .seq) IN (

SELECT

peopleid,

seq

FROM

vitae

GROUP BY

peopleid,

seq

HAVING

COUNT (*) > 1

)

4、删除表中多余的重复记录(多个字段)，只留有rowid最小的记录

DELETE

FROM

vitae A

WHERE

(A .peopleid, A .seq) IN (

SELECT

peopleid,

seq

FROM

vitae

GROUP BY

peopleid,

seq

HAVING

COUNT (*) > 1

)

AND ROWID NOT IN (

SELECT

MIN (ROWID)

FROM

vitae

GROUP BY

peopleid,

seq

HAVING

COUNT (*) > 1

)

5、查找表中多余的重复记录(多个字段)，不包含rowid最小的记录

SELECT

*

FROM

vitae A

WHERE

(A .peopleid, A .seq) IN (

SELECT

peopleid,

seq

FROM

vitae

GROUP BY

peopleid,

seq

HAVING

COUNT (*) > 1

)

AND ROWID NOT IN (

SELECT

MIN (ROWID)

FROM

vitae

GROUP BY

peopleid,

seq

HAVING

COUNT (*) > 1

)