味味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，提高效率。