假設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,提高效率。

味味A 發表在 痞客邦 留言(0) 人氣()