如何删除重复的记录
创建的时候没加约束条件,插入的时候失误,把成绩插入了2遍,
如何删除重复的啊?
[解决办法]
select distinct * into #tmp from 表
delete from 表
insert into 表 select * from #tmp
drop table #tmp
[解决办法]
select distinct * into #tmp from 表
GO
delete from 表
GO
insert into 表 select * from #tmp
GO
drop table #tmp
GO
[解决办法]
alter table table_name add id int identity(1,1)
delete from table_name where id not in(select min(id) from table_name )
alter table table_name drop column id
[解决办法]
没有自增的处理方法:
列名:col1,col2重复
alter table t1 add row int identity(1,1)--增列标识列
go
delete t from t1 t where exists(select 1 from t1 where col1=t.col1 and col2=t.col2 and row>t.row)
或
delete t1 where row not in(select max(row) from t1 group by checksum(col1,col2))
--保留后一条
delete t from t1 t where exists(select 1 from t1 where col1=t.col1 and col2=t.col2 and row<t.row)
或
delete t1 where row not in(select min(row) from t1 group by checksum(col1,col2))
--保留前一条
