先设置一个自增id,删除数据后再删除自增id字段 delete from keyColumn where id in ( select max(id) from testTable group by keyColumn having count(*)>1 )
假设字段2为image类型--将数据插入临时表 select identity(int,1,1) id,* into # from 表--清空源表数据 truncate table 表--插入数据 insert 表 select 字段1,字段2 from # t where id=(select min(id) from # where 字段1=t.字段1 and convert(varchar(8000),字段2)=convert(varchar(8000),t.字段2))
然后,如果count(*)大于1的话,就根据PKCol名称删除一笔就可以了
用binnary类型的思考,而不局限在image类型
比如把他先转化为一中类型后处理
然后转化回来
delete from keyColumn
where
id in
(
select
max(id)
from
testTable
group by keyColumn
having count(*)>1
)
select identity(int,1,1) id,* into # from 表--清空源表数据
truncate table 表--插入数据
insert 表
select 字段1,字段2
from # t
where id=(select min(id)
from #
where 字段1=t.字段1
and
convert(varchar(8000),字段2)=convert(varchar(8000),t.字段2))