MSSQL删除重复记录

Method1

--检查表是否存在

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_paper_info_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test_paper_info_temp]
GO

--新建一个和原表结构相同的表，下面的结构可以直接从数据表生成SQL脚本中获得
--注意：下面这行最后的表名要加上 _temp
CREATE TABLE [dbo].[test_paper_info_temp] (
 [paper_id] [int] NOT NULL ,
 [questionid] [int] NOT NULL ,
 [selectid] [int] NULL ,
 [classid] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [classname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [qname] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [choice1] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [choice2] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [choice3] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [choice4] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [choice5] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [choice6] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [answer] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
 [timu_type] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
 [type_score] [int] NULL
) ON [PRIMARY]
GO

--新建一个临时表的索引，并“忽略重复的值”,xxx作为唯一表识值字段
CREATE  UNIQUE  INDEX [temp] ON [dbo].[test_paper_info_temp]([paper_id]) WITH  IGNORE_DUP_KEY  ON [PRIMARY]
GO

--把原表的数据导入临时表，XXX为原表名
--insert into test_paper_info_temp Select * from test_paper_info
insert into test_paper_info_temp Select * test_paper_info_info

--清空原表，并将临时表中的数据导回原表，最后删除临时表
delete test_paper_info

insert into test_paper_info select * from test_paper_info_temp
drop table test_paper_info_temp

 

Method2

例如数据
a    b
1    张三
2    李四
1    张三
2    赵六
2    李四

处理后为:
a    b
1    张三
2    李四
2    赵六

1.使用游标逐条删除记录,删除数目为重复记录的记录数减去1
set nocount on
declare @count int
declare @a int,@b varchar(50)
declare tmpcursor CURSOR for
select a,b,count(*) from [testtable] group by a,b having count(*)>1
open tmpcursor
fetch next from tmpcursor into @a,@b,@count
while @@fetch_Status=0
begin

set @count=@count-1
print @count
set rowcount @count
delete from testtable where a=@a and b=@b
fetch next from tmpcursor into @a,@b,@count
end
close tmpcursor
deallocate tmpcursor
go
set rowcount 可以让SQL Server在返回指定行数后停止处理查询,因此仅剩一条记录
不会删除

2.新增一个标识列,利用它来删除重复记录
alter table [testtable]
add key_col int not null identity(1,1)
delete from [testtable]
where exists
(
select * from [testtable] as t2
where t2.a=[testtable].a and t2.b=[testtable].b
and t2.key_col>[testtable].key_col
)
alter table [testtable]
drop column key_col