先把identity列删去.然后在查询分析器中select distinct * into #a from tablename
truncate table tablename
insert tablename select * from #a
drop table #a
然后加上identity列
truncate table tablename
insert tablename select * from #a
drop table #a
然后加上identity列
调试欢乐多
where id not in
(select min(id)
from mytable
group by checksum(field1,field2,field3)) t0
或
delete from mytable
where id left join
(select min(id) as minid
from mytable
group by checksum(field1,field2,field3)) t0
on id=t0.minid and t0.minid is nullfield1,field2,field3是所有除id以外的字段。第二句效率会高些。
你的办法我也想到用过,但是由于该表的id列同时是此表的主键,与其他表之间有关联。用上面的方法,会不会将正确的一条删掉了而保留了错误的一条,从而导致id不对而破坏数据库的参照完整性。
icevi(按钮工厂) :
多谢!用你的第一种办法搞定!
第二种方法有问题。delete from mytable
where id left join
(select min(id) as minid
from mytable
group by checksum(field1,field2,field3)) t0
~~~~
出错提示指示t0有问题,这里t0是代指mytable还是代指checksum(f1,f2,f3)on id=t0.minid and t0.minid is nullfield1,field2,field3是所有除id以外的字段。第二句效率会高些。