sql中删除重复行(所有列的数据都是相同的只保留一条数据)

  1.要删除所有列都是相同的重复行，并且ID不是自动增长列，主键列，要想只保留一行数据，最简单有效的方法就是用临时表进行删除

 sql：

       select distinct * into #tmp from tablename

       drop table tablename

       select * into tablename from #tmp

       drop table #tmp

2.用游标进行删除

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from tablename group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from tablename where 主字段= @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0