＋设为首页 ＋添加到收藏夹
现有一表
现要求SQL语句查询出表中所有重复的记录。最后删除。
查询:
select * from tablename
where A+B*C in (select A+B*C from tablename group by A+B*C having count(*)>1)
楼上正确
删除：
delete x
from tablename x
where A+B*C in (select A+B*C from tablename group by A+B*C having count(*)>1)
and (a<>(select min(a) from tablename where A+B*C=x.A+x.B*x.C)
or b<>(select min(b) from tablename where A+B*C=x.A+x.B*x.C and a=x.a)
or c<>(select min(c) from tablename where A+B*C=x.A+x.B*x.C and a=x.a and b=x.b)
or d<>(select min(d) from tablename where A+B*C=x.A+x.B*x.C and a=x.a and b=x.b and c=x.c)
or e<>(select min(e) from tablename where A+B*C=x.A+x.B*x.C and a=x.a and b=x.b and c=x.c and d=x.d)
or f<>(select min(f) from tablename where A+B*C=x.A+x.B*x.C and a=x.a and b=x.b and c=x.c and d=x.d and e=x.e)
or g<>(select min(g) from tablename where A+B*C=x.A+x.B*x.C and a=x.a and b=x.b and c=x.c and d=x.d and e=x.e and f=x.f)
)
注：如果有主键，没有这么麻烦。
select A，B，C，D，E，F，G,a+B*C as x into #t1 from tbname
select distinct x into #t2 from #t1
--delete table
drop table tbname
--rebuild table
select #t1.* into tbname from #t1 inner join #t2
on #t1.x=#t2.x
delete from tablename where field1 not in (
select field1 from tablename as tn where field1 in (
select top 1 field1 from tablename where field2=tn.field2))
