SQL语句删除重复记录
来源:互联网 发布:mysql运维内参pdf下载 编辑:程序博客网 时间:2024/04/29 23:09
此文章用了四种方法教你如何用SQL语句删除重复记录。
问题:怎样把具有相同字段的纪录删除,只留下一条。
例如:表test里有id,name字段,如果有name相同的记录只留下一条,其余的删除。name的内容不定,相同的记录数不定。
方案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 '+@f_key+' ,count(*) from '
+@t_name +' group by ' +@f_key +' 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 name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+'
where ' + @f_key+' = '+ @id
if @type=167
select @sql = 'delete from '+@t_name+'
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语句删除重复记录
- sql删除重复记录语句
- SQL语句删除重复记录
- SQL语句删除重复记录
- SQL语句删除重复记录
- SQL 语句删除access重复记录语句
- 几个删除重复记录的SQL语句!
- 几个删除重复记录的SQL语句
- SQL语句快速删除重复记录
- 一题多解教你SQL语句删除重复记录
- 几个删除重复记录的SQL语句
- 用SQL语句删除重复记录
- 几个删除重复记录的SQL语句
- 删除重复记录的sql语句
- 可以删除重复记录的SQL语句
- 几个删除重复记录的SQL语句
- 删除重复记录的SQL语句
- 几个删除重复记录的SQL语句
- 海蜘蛛软路由制作U盘启动盘
- 数组玩法(1):下标移位
- zenoss 介绍
- SQL Server中优化其索引的妙招
- SQL Server加密与SQL注入
- SQL语句删除重复记录
- Vi Family
- Carbide C++ 出现Save could not be completed!的解决方法
- 最基本财务基础知识,财务知识基础来源
- 让SQL Server为工作负载高峰提前做好准备
- SQL Server 2005 数据库镜像介绍
- SQL Server开发人员应聘常被问的问题妙解汇总
- 使用T-SQL操作面试SQL Server开发人员
- ITIL的開源軟件