写错了点,应该这样:
delete from t where exists(select * from t a where a.f1=t.f1 and a.f2=t.f2 and a.id>t.id)
下面是测试语句:
create table #t(ID int,F1 varchar(20),F2 varchar(20),F3 varchar(20))
insert into #t(ID,F1,F2,F3)
select 1,'a','1a','cc' union
select 2,'a','1a','ccc' union
select 3,'b','1a','cccc' union
select 4,'b','1b','ccccc' union
select 5,'b','1b','cccccc' union
select 6,'b','1b','ccccccc' union
select 7,'c','2c','cccccccc'
delete from #t where exists(select * from #t a where a.f1=#t.f1 and a.f2=#t.f2 and a.id>#t.id)
select * from #t
drop table #t