怎样删除一个表中一个字段具有相同值的其中一条记录

[咏帆]

怎样删除一个表中一个字段具有相同值的其中一条记录
如：表abc中有字段a,b,c
a字段中有相同有值
怎么样删除含有相同值的记录，只保留最前的记录~

[咏帆]

路过的帮忙看下哈~~~~~~~

[QQMagicer]

delete from t1
where a <> ( select top 1 a from t1 group by id having count(a)>1 order by a)

[QQMagicer]

--不好意思，上面那个发错了
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)


这个也不对

[Mr_shao]

delete from (select count(a) as a1 form t1) tt where tt.a1>0

试试吧，前几天写过但记不太清了！

[hhwufeng]

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
)
)