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

admin

conn.execute("delete from table where id not in (select distinct from table)") 文件删除函数 <% 0 Y p+ j8 T, `'文件删除函数 function deletefile(filename) + \/ L; F) Z/ Z( @if filename<>"" then set fso=server.CreateObject("scripting.filesystemobject") ! b6 G9 ~; y& ]5 i, c+ hif fso.FileExists(filename) then fso.DeleteFile filename else 2 E) \* s( a" @* M* f" l( l: r; {Response.Write "<script>alert(’该文件不存在’);</script>" end if end if end function 7 r0 p2 g: @/ N! w. l! t5 w0 L3 ~, X 3 G0 v! d6 t- \6 Fstrfile=server.MapPath("fileName") ; X( Y- j2 u) q# t0 ideletefile(strfile) 1 A1 f7 |; k( E. V9 N! i%>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people ) W, \2 _ Z6 @# pwhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 - X2 ?2 L! c+ J' b6 q+ c: Jdelete from people . ~1 \6 d" N; r$ vwhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) " B/ n% a% G/ {; X3 x0 aand rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录（多个字段） 0 T3 i$ t0 @" A2 W) |! O) Yselect * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录 delete from vitae a $ U: r/ `/ C5 s0 `, r- Vwhere (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) : \) {. Q& o5 o% }5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 % {4 z* e4 @3 M+ uselect * from vitae a 8 J7 G7 H/ k& ^7 ?) @where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) , S& Q, j1 P7 X! x* Zand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) ' e2 p8 ]/ n, o9 D8 }(二) 比方说 1 T7 y! h. ^* b6 ]5 N在A表中存在一个字段“name”， 而且不同记录之间的“name”值有可能会相同， 9 N4 |" S& A* X% d) n现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； Select Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: " p7 `+ p& \/ s) ~Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 方法一 # t% n$ Y7 o& [. v: n% _ , n, u4 \+ f/ z, S* Adeclare @max integer,@id integer " _* Z8 w1 q* e0 A& y4 X# _ declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 0 v5 B: X4 A& ~1 d$ ?" X+ y open cur_rows ( V6 A9 B7 M' Z2 x, t . r! t5 M/ {3 P0 ~% r0 ^/ efetch cur_rows into @id,@max ( [- b0 {* n9 A% X* O& hwhile @@fetch_status=0 begin % \) f+ ~+ A5 Yselect @max = @max -1 ) a( W+ `) G- j3 U( M4 B 2 w3 ~8 `4 t8 l' ^0 mset rowcount @max % Q- \0 {, B( g+ I7 X& F delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end 9 I: @- |0 }& Z+ Y" g+ G/ m2 Jclose cur_rows ' F' [9 n. E. t, h # h% ]6 i/ F& v! a8 n# o8 s! e) G, tset rowcount 0 方法二 # s; e# R" v( A ＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 ; j- `) i; C. o6 x, @ 　　1、对于第一种重复，比较容易解决，使用 + P, l, R2 d$ J, O+ ` R 7 |9 C, Y6 O( B+ v2 F, A& nselect distinct * from tableName 0 x6 {8 m8 i( w0 I4 ?) G & j6 r8 x. r/ f& ]* b9 `　　就可以得到无重复记录的结果集。 6 a; K/ W6 E# o 　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 " O; C% e; W# m3 ~$ l ; U$ D# z. {6 }5 d9 kselect distinct * into #Tmp from tableName drop table tableName , f/ g5 _) Z9 n4 W9 z# |, k) u select * into tableName from #Tmp * p( X/ S8 L4 t select id from tablename . V3 ?; M3 h: ?' y& j 9 ^& f l7 V h5 R" Y( U' E3 qgroup by id % ^7 q9 V4 o1 s6 Q. d7 I. [ having count(id) > 1 $ g7 x" Q- D' S1 l% r- C" O; J ) o/ s# W; g3 B6 p% [)