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

1、用SQL语句删除重复记录的四种方法(1)问题：如何把具有相同字段的记录删除，只留下一条。例如：表test里有id，name字段，如果有name相同的记录只留下一条，其余的删除。name 的内容不定，相同的记录数不定。用SQL语句删除重复记录的四种方法：方法1：1、将重复的记录记入tempi表select 标志字段 id,count(*) into tempi from 表名group by 标志字段idhaving count(*)12、将不重复的记录记入tempi表insert tempiselect 标志字段 id,count(*) from 表名group by 标志字段idhaving

2、 count(*)=13、作一个包含所有不重复记录的表select * into temp2 from 表名where 标志字段 id in(select 标志字段 id from tempi)4、删除重复表:delete 表名5、恢复表insert 表名select * from temp26、删除临时表drop table tempidrop table temp2方法2：declare max integer,id integerdeclare cur_rows cursor local forselect id,count(*) from 表名 group by id having c

3、ount(*) 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_dis

4、t(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表示是分组字段，即主键字段asbegindecla

5、re 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_rowsfetch cur_rows into id,maxwhile fetch_status=0beginselect max = max -1set rowcount maxselect type

6、= xtype from syscolumnswhere 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 endclose cur_rowsdeallocate cur_rowsset rowcount 0endselect * fro

7、m 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)goinsertintodup(name)values(abc)insertintodup(name)values(abc)insertintodup(name)values(abc)insertintodup(name)values(abc)insertintodup(name)valu

8、es(abc)insertintodup(name)values(abc)insertintodup(name)values(abc)insertintodup(name)values(cdefg)insertintodup(name)values(xyz)insertintodup(name)values(xyz)go select * from dup gocreate 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, name from dup go select * from tempdb.wk go delete from dup go set identity_insert dup onINSERT INTO dup (id, name)