我有个表,可是里面有好多记录都是重复的。请教各位如何删除?每个记录只留一条?------------- delete t where exists(select 1 from t where name=a.name and id<a.id)
如果所有字段都有重復select distinct * into #t from tdelete tinsert into t select * from #t
如果是完全相同的话使用下面的代码 select distinct * into temptable from yourtable; delete yourtable insert into yourtable select * from temptable drop temptable 如果是某列(如是编号重复) 可使用下面的代码 create proc proc_DeleteRepeatRecord --删除同一表中,某列有相同记录,保留一条 @tableName nvarchar(20), -->>表名 @repeatColumn nvarchar(20) -->>列名 as declare @sql nvarchar(1000)set @sql=(' -->>直接赋给@sql declare @max int,@rowname varchar(100) declare cur_rows cursor for select '+ @repeatColumn +',count(*) from ' + @tableName + ' group by ' + @repeatColumn + ' having count(*) > 1 open cur_rows fetch cur_rows into @rowname ,@max while @@fetch_status=0 begin set @max = @max -1 set rowcount @max delete from ' +@tableName+' where ' +@repeatColumn+' =@rowname fetch cur_rows into @rowname ,@max end close cur_rows deallocate cur_rows') exec(@sql)
delete t
where exists(select 1 from t where name=a.name and id<a.id)
select * from #t
select distinct * into temptable from yourtable;
delete yourtable
insert into yourtable select * from temptable
drop temptable
如果是某列(如是编号重复) 可使用下面的代码
create proc proc_DeleteRepeatRecord
--删除同一表中,某列有相同记录,保留一条
@tableName nvarchar(20), -->>表名
@repeatColumn nvarchar(20) -->>列名
as
declare @sql nvarchar(1000)set @sql=(' -->>直接赋给@sql
declare @max int,@rowname varchar(100)
declare cur_rows cursor for
select '+ @repeatColumn +',count(*) from ' + @tableName + ' group by ' + @repeatColumn + ' having count(*) > 1
open cur_rows
fetch cur_rows into @rowname ,@max
while @@fetch_status=0
begin set @max = @max -1
set rowcount @max
delete from ' +@tableName+' where ' +@repeatColumn+' =@rowname
fetch cur_rows into @rowname ,@max
end
close cur_rows
deallocate cur_rows')
exec(@sql)
select distinct 记录 from 表