--不好意思,上面那个发错了 delete from t1 where a in (select top 1 a from t1 as aa where aa.a >t1.a group by a having count(a)>1 order by a)
delete abc from abc tt where exists(select 1 from abc where a=tt.a and b<tt.b)
--不好意思,上面那个发错了 delete from t1 where a in (select top 1 a from t1 as aa where aa.a >t1.a group by a having count(a)>1 order by a)这个不行
delete abc from abc tt where exists(select 1 from abc where a=tt.a and b<tt.b) 这个也不对
delete from (select count(a) as a1 form t1) tt where tt.a1>0试试吧,前几天写过但记不太清了!
create table #abcd(abc int bbc int) insert #abcd values(1,1) insert #abcd values(2,2) insert #abcd values(3,3) insert #abcd values(3,4) insert #abcd values(3,5) delete from #abcd where bbc in( select a.bbc from #abcd as a join (select abc,count(abc) as tt from #abcd group by abc having count(abc)>1) as b on a.abc = b.abc and a.bbc <> ( select top 1 a.bbc from #abcd as a join (select abc,count(abc) as tt from #abcd group by abc having count(abc)>1) as b on a.abc = b.abc ) )
--不好意思,上面那个发错了
delete from t1
where a in
(select top 1 a from t1 as aa
where aa.a >t1.a
group by a
having count(a)>1
order by a)
from abc tt
where exists(select 1 from abc where a=tt.a and b<tt.b)
delete from t1
where a in
(select top 1 a from t1 as aa
where aa.a >t1.a
group by a
having count(a)>1
order by a)这个不行
from abc tt
where exists(select 1 from abc where a=tt.a and b<tt.b)
这个也不对
insert #abcd values(1,1)
insert #abcd values(2,2)
insert #abcd values(3,3)
insert #abcd values(3,4)
insert #abcd values(3,5)
delete from #abcd where bbc in(
select a.bbc from #abcd as a join
(select abc,count(abc) as tt from #abcd group by abc having count(abc)>1) as b
on a.abc = b.abc and a.bbc <>
(
select top 1 a.bbc from #abcd as a join
(select abc,count(abc) as tt from #abcd group by abc having count(abc)>1) as b
on a.abc = b.abc
)
)