creat table tbl_tmp (select distinct* from tbl); truncate table tbl;//清空表记录 insert into tbl select * from tbl_tmp;//将临时表中的数据插回来。 |
delete from tbl where rowid in ( select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) |
delete from tbl awhere rowid not in ( select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里max使用min也可以 |
delete from tbl awhere rowid<(select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2); //这里如果把max换成min的话,前面的where子句中需要把"<"改为">" |
deletefrom tbl where rowid not in (select max(rowid) from tbl tgroup by t.col1, t.col2); delete from tbl where (col1, col2) in (select col1,col2 from tblgroup bycol1,col2havingcount(*) >1) and rowidnotin(selectnin(rowid)fromtblgroup bycol1,col2havingcount(*) >1) |
欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/) | Powered by Discuz! X3.2 |