味味A

假設Table為Double_tab,Table內D01,D02為Primary key。

• 使用Temp Table方式
  

  creat table Double_tab_tmp 
  (select distinct* from Double_tab); 
  
  truncate table Double_tab; 
  insert into Double_tab select * from Double_tab_tmp;
  

  這種方法很慢。

   

• 利用rowid
  

  delete from Double_tab 
   where rowid in (select a.rowid 
   from Double_tab a, Double_tab b 
   where a.rowid>b.rowid and a.D01=b.D01 
   and a.D02 = b.D02);
  

  適用於每個key值重覆記錄不多的狀況。

   

• 利用max或min函數
  Not in用法:
  

  delete from Double_tab a 
   where rowid not in (select max(b.rowid) 
   from Double_tab b 
   where a.D01=b.D01 and a.D02 = b.D02);
  

   

  < 或是 >用法:
  

  delete from Double_tab a 
   where rowid < 
   (select max(b.rowid) from Double_tab b 
   where a.D01=b.D01 and a.D02 = b.D02);
  

   

  若max換成min,需把<改為>
  

  delete from Double_tab 
   where rowid not in (select max(rowid) 
   from Double_tab t 
   group by t.D01, t.D02 );
  

  註:使用了group by,提高效率。