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

1、用SQL語句刪除重復記錄的四種方法(1)問題：如何把具有相同字段的記錄刪除，只留下一條。例如：表test里有id，name字段，如果有name相同的記錄只留下一條，其余的刪除。name 的內(nèi)容不定，相同的記錄數(shù)不定。用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表示當前緩沖區(qū)只容納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)