問題：如何把具有相同欄位的記錄刪除，只留下一條。

例如：表test裡有id，name欄位，如果有name相同的記錄只留下一條，其餘的刪除。name的內容不定，相同的記錄數不定。

用SQL語句刪除重複記錄的四種方法：

方法1：

1、將重複的記錄記入temp1表

select [標誌欄位id],count(*) into temp1 from [表名]
group by [標誌欄位id]
having count(*)>1

2、將不重複的記錄記入temp1表

insert temp1
select [標誌欄位id],count(*) from [表名]
group by [標誌欄位id]
having count(*)=1

3、作一個包含所有不重複記錄的表

select * into temp2 from [表名]
where 標誌欄位id in(select 標誌欄位id from temp1)

4、刪除重複表:delete [表名]

5、恢復表

insert [表名]
select * from temp2

6、刪除臨時表

drop table temp1
drop table temp2

方法2：

declare @max integer,@id integer
declare cur_rows cursor local for
select id,count(*) from 表名 group by id 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 表名 where id = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

注：set rowcount @max - 1表示當前緩衝區只容納@max-1條記錄，如果有十條重複的，就刪除10條，一定會留一條的。也可以寫成delete from 表名。

方法3：

create table a_dist(id int,name varchar(20))
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
exec up_distinct 'a_dist','id'
select * from a_dist
create procedure up_distinct(@t_name varchar(30)
,@f_key varchar(30))
--f_key表示是分組欄位﹐即主鍵欄位
as
begin
declare @max integer,@id varchar(30) ,
@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor 
for select  ,count(*) from ' 
 +' group by '  +' having count(*) > 1'
exec(@sql)
open cur_rows 
fetch cur_rows into @id,@max 
while @@fetch_status=0 
begin 
select @max = @max -1 
set rowcount @max 
select @type = xtype from syscolumns 
where id=object_id(@t_name) and 
if @type=56
select @sql = 'delete from  
where ' + @f_key+' = '+ @id 
if @type=167
select @sql = 'delete from  
where ' + @f_key+' = '+''''+ @id +'''' 
exec(@sql)
fetch cur_rows into @id,@max 
end 
close cur_rows 
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where 
id = object_id('a_dist')

方法4：

可以用IGNORE_DUP_KEY： 

create table dup (id int identity not null,
name varchar(50)not null)
go
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('cdefg')
insert into dup(name) values ('xyz')
insert into dup(name) values ('xyz')
go
select *
from dup
go
create table tempdb..wk(id int not null, 
name varchar(50)not null)
go
create unique index idx_remove_dup 
on tempdb..wk(name)
with IGNORE_DUP_KEY 
go
INSERT INTO tempdb..wk (id, name)
select id, name
from dup
go
select *
from tempdb..wk
go
delete from dup
go
set identity_insert dup on
INSERT INTO dup (id, name)
select id, name
from tempdb..wk
go
set identity_insert dup off
go
select *
from dup
go

註釋：此處delete原表，再加入不重複的值。大家也可以透過join只delete原表中重複的值。

用SQL語句刪除重複記錄的四種方法

相關文章