mysql用一条sql语句删除重复记录_用SQL语句删除重复记录的四种方法_MySQL

问题:如何把具有相同字段的记录删除,只留下一条。

例如:表test里有id,name字段,如果有name相同的记录只留下一条,其余的删除。name的内容不定,相同的记录数不定。

用SQL语句删除重复记录的四种方法:

方法1:

1、将重复的记录记入temp1表

select [标志字段id],count(*) into temp1 from [表名]group by [标志字段id]having count(*)>1

2、将不重复的记录记入temp1表

insert temp1select [标志字段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 temp1drop table temp2

方法2:

declare @max integer,@id integerdeclare cur_rows cursor local for select id,count(*) from 表名 group by id having count(*) > 1open cur_rowsfetch cur_rows into @id,@maxwhile @@fetch_status=0beginselect @max = @max -1set rowcount @maxdelete from 表名 where id = @idfetch cur_rows into @id,@maxendclose cur_rowsset 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_distcreate procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分组字段﹐即主键字段asbegindeclare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integerselect @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_keyif @type=56select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql)fetch cur_rows into @id,@max end close cur_rows deallocate cur_rowsset rowcount 0endselect * from systypesselect * 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)goinsert 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')goselect *from dupgocreate table tempdb..wk(id int not null, name varchar(50)not null)gocreate unique index idx_remove_dup on tempdb..wk(name)with IGNORE_DUP_KEY goINSERT INTO tempdb..wk (id, name)select id, namefrom dupgoselect *from tempdb..wkgodelete from dupgoset identity_insert dup onINSERT INTO dup (id, name)select id, namefrom tempdb..wkgoset identity_insert dup offgoselect *from dupgo

注释:此处delete原表,再加入不重复的值。大家也可以通过join只delete原表中重复的值

本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源:php中文网

TAG标签:记录如何

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值