由于一张表中只有一列,所以只好采用如下方式:
select identity(int,1,1) as autoID, columnName ,count( columnName) sum into #temp from tableName
group by columnName
having count( columnName) > 1
// 查处columnName这一列重复的数据,并保存在一张临时表#temp中,且添加了autoID字段
select * from #temp //查看临时表的数据
delete tableName where columnName in (
Select columnName from #temp
)
//删除表中的重复全部数据,一行不留
insert into tableName( columnName)
Select columnName from #temp
//插入重复数据,使得重复数据保留一行
select columnName from tableName group by columnName
Having count( columnName)>1
//查看结果
drop table #temp
//删除临时表