sql 重複資料刪除行

標籤：sql   text   rom   a   3   select   記錄   class   

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)