检索并删除数据库里的重复记录

admin

conn.execute("delete from table where id not in (select distinct from table)") 文件删除函数 <% '文件删除函数 function deletefile(filename) if filename<>"" then set fso=server.CreateObject("scripting.filesystemobject") if fso.FileExists(filename) then fso.DeleteFile filename else Response.Write "<script>alert(’该文件不存在’);</script>" end if @+ i% o5 v9 p; mend if & S. ^2 I N9 S4 A8 n7 m! Q* v% ]) \end function . u# m% }/ L5 J9 v* k% x2 p" j strfile=server.MapPath("fileName") 5 X9 b/ H+ p* R( f. a# N3 Hdeletefile(strfile) %>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 ( ?+ {! w# N* S: G2 n; hselect * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 6 ]7 @2 _2 M' |/ M) H( K2 Kdelete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) + a1 d5 y9 t% D5 r! V8 [8 ]and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录（多个字段） select * from vitae a $ e5 b% C, ]" i( `1 ywhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录 7 c) l5 S# w4 G$ h. Fdelete from vitae a , ~1 T0 D. A( B; b7 A2 K4 @" nwhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) ' O# c$ F- z- |1 k2 ~' O5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 % l9 l* H( _+ Z( {select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 5 e5 L- x+ O& ]and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) (二) 比方说 在A表中存在一个字段“name”， . u8 ^; g& j3 J+ \! G" g) e而且不同记录之间的“name”值有可能会相同， 现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； ; F T7 h* D/ v$ Y2 k. RSelect Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: " [( }) ?6 ~2 \Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 5 Y3 E) g/ e' J4 M7 a方法一 # ]. H! |7 s8 a( p. ~: L declare @max integer,@id integer . }6 o3 {# p* n declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 ; R4 h; T8 i* M+ ?open cur_rows fetch cur_rows into @id,@max * Q0 f! ^8 f3 W+ W# W. ?1 h% Lwhile @@fetch_status=0 begin select @max = @max -1 set rowcount @max - @- f1 }# q( t. x4 Z delete from 表名 where 主字段 = @id W- n6 R% P! C8 N6 _fetch cur_rows into @id,@max end / b `& U2 B W7 [9 E- w) K# ]! S' Y# Y 8 I+ w4 y, u8 `& S3 r. u4 Bclose cur_rows & u% ?7 q- D% S ! G5 f" e+ L& x$ y f, }set rowcount 0 方法二 ( y; S- N m( G$ Y* |1 N+ b＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 , M2 H8 e& ?- j' s. j8 S/ |2 m 3 t- w6 U5 {+ y; _. h0 p　　1、对于第一种重复，比较容易解决，使用 0 O/ J; ]* y& Z/ w5 D+ H- Uselect distinct * from tableName 　　就可以得到无重复记录的结果集。 9 _8 V$ H }+ ^5 Y% n8 f# s 　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 " q( _0 i* b: T, D4 ^: Q5 P select distinct * into #Tmp from tableName . d) J4 i) H1 h drop table tableName 7 H% E3 B7 I3 B# U2 `: Y2 Z! X select * into tableName from #Tmp 3 }; [1 V! c8 ^select id from tablename " `' m% U8 H# `/ T- q, ]8 W( j/ v group by id - [0 @2 _# i" G. W: j" f: q6 L$ | having count(id) > 1 9 ]& S1 T& ^: }! @ 4 ^8 ]/ z5 O1 X9 C)