oracel 查詢重複資料刪除記錄的幾種方法

標籤：ble   style   person   重複記錄   having   order by   group   欄位   not   

建表語句
CREATE TABLE Persons
(
PersonID int,           
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

insert into Persons values(1,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(1,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(1,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(1,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(2,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(2,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(3,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(3,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(4,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);
insert into Persons values(5,‘a‘,‘aa‘,‘aaa‘,‘aaaa‘);

select * from Persons order by PersonID;
    PERSONID    LASTNAME    FIRSTNAME    ADDRESS   　　 CITY
1    1   　　　　　　 a    　　　　aa    　　　　　　aaa   　　 aaaa
2    1   　　　　　　 a   　　　　 aa    　　　　　　aaa   　　 aaaa
3    1   　　　　　　 a    　　　　aa   　　　　　　 aaa    　　aaaa
4    1   　　　　　　 a    　　　　aa    　　　　　　aaa    　　aaaa
5    2   　　　　　　 a   　　　　 aa    　　　　　　aaa    　　aaaa
6    2   　　　　　　 a   　　　　 aa    　　　　　　aaa    　　aaaa
7    3    　　　　　　a    　　　　aa    　　　　　　aaa    　　aaaa
8    3    　　　　　　a    　　　　aa    　　　　　　aaa   　　 aaaa
9    4   　　　　　　 a    　　　　aa    　　　　　　aaa    　　aaaa
10    5   　 　　　　 a    　　　　aa    　　　　　　aaa    　　aaaa


1、尋找表中多餘的重複記錄，重複記錄是根據單個欄位（PersonID）來判斷。
select * from Persons where PersonID in ( select PersonID from Persons group by PersonID having(count(PersonID))>1);
order by PersonID;

PERSONID    LASTNAME    FIRSTNAME    ADDRESS    CITY
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
2    a    aa    aaa    aaaa
2    a    aa    aaa    aaaa
3    a    aa    aaa    aaaa
3    a    aa    aaa    aaaa


2、刪除表中多餘的重複記錄，重複記錄是根據單個欄位（PersonID）來判斷，只留有rowid最小的記錄。
select * from Persons
where PersonID  in (select  PersonID  from Persons  group  by  PersonID   having  count(PersonID) > 1)
and rowid not in (select min(rowid) from  Persons  group by PersonID  having count(PersonID )>1)
order by PersonID;

delete from Persons
where PersonID  in (select  PersonID  from Persons  group  by  PersonID   having  count(PersonID) > 1)
and rowid not in (select min(rowid) from  Persons  group by PersonID  having count(PersonID )>1)

PERSONID    LASTNAME    FIRSTNAME    ADDRESS    CITY
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
2    a    aa    aaa    aaaa
3    a    aa    aaa    aaaa


3、尋找表中多餘的重複記錄（多個欄位）
select * from Persons a
where (a.PersonID,a.City) in  (select PersonID,City from Persons group by PersonID,City  having count(*) > 1)
order by PersonID;

PERSONID    LASTNAME    FIRSTNAME    ADDRESS    CITY
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
2    a    aa    aaa    aaaa
2    a    aa    aaa    aaaa
3    a    aa    aaa    aaaa
3    a    aa    aaa    aaaa


4、刪除表中多餘的重複記錄（多個欄位），只留有rowid最小的記錄
select * from Persons a
where (a.PersonID,a.City) in  (select PersonID,City from Persons group by PersonID,City having count(*) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID,City having count(*)>1)
order by PersonID;

delete from Persons a
where (a.PersonID,a.City) in  (select PersonID,City from Persons group by PersonID,City having count(*) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID,City having count(*)>1)

PERSONID    LASTNAME    FIRSTNAME    ADDRESS    CITY
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
2    a    aa    aaa    aaaa
3    a    aa    aaa    aaaa


5、尋找表中多餘的重複記錄（多個欄位），不包含rowid最小的記錄
select * from Persons a
where (a.PersonID,a.City) in  (select PersonID,City from Persons group by PersonID,City having count(*) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID,City having count(*)>1)
and rowid not in (select min(rowid) from Persons group by PersonID,seq having count(*)>1)
order by PersonID;

PERSONID    LASTNAME    FIRSTNAME    ADDRESS    CITY
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
1    a    aa    aaa    aaaa
2    a    aa    aaa    aaaa
3    a    aa    aaa    aaaa

oracel 查詢重複資料刪除記錄的幾種方法