這一生只為妳

如果一個TABLE的資料是這樣

        ID NAME                                  NUM
---------- ------------------------------------- ----------
         1 AAA                                   10000
         1 AAA                                   10000
         2 BBB                                   20000
         2 BBB                                   20000
         3 CCC                                   30000
         2 BBB                                   20000

用以下的SQL可以查詢

select * from TABLEA t1 where rowid in(select max(rowid)from TABLEA t2 where t1.id=t2.id
and t1.name=t2.name and t1.num=t2.num);        

        ID NAME                                  NUM
---------- ------------------------------------- ----------
         1 AAA                                   10000
         3 BBB                                   30000
         2 CCC                                   20000


刪除的方法︰


1）透過建立臨時表來實現

新增一個臨時TABLE，並將原資料複製到臨時TABLE
create table temp_tablea as (select distinct * from TABLEA)   

清空原TABLEA的數據
truncate table TABLEA;

再將臨時TABLE裡的內容插回來
insert into TABLEA select*from temp_tablea;



2）使用內建的rowid，如果查出來的rowid小於最大值的rowid，即可刪除

delete from TABLEA T2 where rowid < (select max(T1.rowid)
from TABLEA T1
where T1.id=T2.id and T1.name=T2.name and T1.num=T2.num);