delete b from tb a,tb b where a.a=b.a and a.b=b.b and len(a.c)<len(b.c)
delete * from tb a where not exists (select 1 from tb where a.a=b.a and a.b=b.b and len(a.c)>len(b.c) )
create table A ( A int, B int, C varchar(10) )insert A select 1,2,'大师父' insert A select 1,2,'大师父面条' insert A select 2,3,'爱敌深'delete T from A T where exists(select 1 from A where A=T.A and B=T.B and len(C)<len(T.C))
可能有 NULL 在里面!eg:
create table A ( A int, B int, C varchar(10),) insert A select 1,Null,'大师父' insert A select 1,Null,'大师父面条' insert A select 2,3,'爱敌深' delete T from A T where exists(select 1 from A where A=T.A and B=T.B and len(C)<len(T.C))select * from A
try: isnulldelete T from A T where exists(select 1 from A where A=T.A and isnull(B,0)=isnull(T.B,0) and len(C)<len(T.C))字符的话 就 isnull(字段名,'')
加个自动编号列就可以搞定了. Delete from A Where ID Not In (Select Min(ID) from A Group By A,B,C )
from tb a
where not exists
(select 1
from tb
where a.a=b.a and a.b=b.b and len(a.c)>len(b.c)
)
(
A int,
B int,
C varchar(10)
)insert A select 1,2,'大师父'
insert A select 1,2,'大师父面条'
insert A select 2,3,'爱敌深'delete T from A T
where exists(select 1 from A where A=T.A and B=T.B and len(C)<len(T.C))
(
A int,
B int,
C varchar(10),)
insert A select 1,Null,'大师父'
insert A select 1,Null,'大师父面条'
insert A select 2,3,'爱敌深'
delete T from A T
where exists(select 1 from A where A=T.A and B=T.B and len(C)<len(T.C))select * from A
isnulldelete T from A T
where exists(select 1 from A where A=T.A and isnull(B,0)=isnull(T.B,0) and len(C)<len(T.C))字符的话 就 isnull(字段名,'')
Delete from A Where ID Not In (Select Min(ID) from A Group By A,B,C )