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

admin

conn.execute("delete from table where id not in (select distinct from table)") ; O$ [. B0 A6 J3 j文件删除函数 <% / n/ N$ h0 j# N9 R w& t2 j'文件删除函数 function deletefile(filename) ' X+ s. s/ `0 [+ F- t* q! fif filename<>"" then set fso=server.CreateObject("scripting.filesystemobject") if fso.FileExists(filename) then Y: F) z# n5 _5 zfso.DeleteFile filename else 2 |8 R% S; M- T1 V6 y6 ~Response.Write "<script>alert(’该文件不存在’);</script>" . R4 C& x6 F' ^( a! Z- d8 oend if H* \2 `+ `+ G' x8 bend if end function strfile=server.MapPath("fileName") - `: Z' a2 d" H1 T+ L0 cdeletefile(strfile) ! r6 W& e% K# y& _ y1 t5 F%>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 ' _3 S/ H" M1 S. l; mselect * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 & z& g( n6 o2 ?" v- z0 Z* ^- ndelete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录（多个字段） select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录 ) `2 @1 k4 o& Q& idelete from vitae a where (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) 5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 9 { m1 |' R, X( b9 l& m3 ^4 [, ?& nselect * from vitae a where (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) (二) 7 ^+ M! G4 @8 r$ y1 f0 e8 A比方说 在A表中存在一个字段“name”， : M7 x+ ~! _4 O7 Q3 u e, }6 o而且不同记录之间的“name”值有可能会相同， $ f4 T- y, ?6 { K# w; e现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； ' @& m& l9 e0 S, g+ F2 qSelect Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 方法一 & u; F0 G5 Y4 zdeclare @max integer,@id integer 3 ], L3 h( W! Y declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 2 g/ [7 {" f& y% Y( w & ?! a [+ E8 ~7 |$ f2 Wopen cur_rows 4 y* A- V E2 B& U1 bfetch cur_rows into @id,@max . k: ?+ m# I0 L5 s7 x5 Q7 O2 M5 F d2 J2 Y8 J, Cwhile @@fetch_status=0 * e: k) v5 F- I5 ^% R# A# d begin 8 U3 n2 ~4 j; T$ |1 G% u) Aselect @max = @max -1 ) w; g- @6 X0 Mset rowcount @max e" X) v) p7 Q# H2 T qdelete from 表名 where 主字段 = @id ! r- {. b$ @: F5 A, `1 w* W ' s( q% P; e0 M8 ^# E$ xfetch cur_rows into @id,@max u% E1 h2 B# f1 Mend 6 f: p; H9 z' Z4 J# A6 yclose cur_rows ! P6 `" E. \. n- t( A/ P$ x set rowcount 0 " {* b \5 f' t 方法二 : B+ t4 o+ N! z * Y% K# ]5 d, T. S3 U7 V＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 X% Q1 D5 i# G7 ^ 　　1、对于第一种重复，比较容易解决，使用 ' z+ L# Z( T% A3 a" Aselect distinct * from tableName 8 R4 v* T8 L# t0 s7 U3 W) V: O: n 　　就可以得到无重复记录的结果集。 " W/ `9 P! B) h5 I, B　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 + r# l' K5 N/ C& [! W, P7 H6 U) Z0 e ; l# w/ U9 N% [% {" zselect distinct * into #Tmp from tableName 5 k* \! f! b( d# u: \ 4 t* ], J% {5 S7 l4 j6 r; T2 B8 udrop table tableName / `0 f/ I7 L y- o ( P- H3 _4 m. l: Pselect * into tableName from #Tmp c% R7 U( f6 e; B; a% f: D8 k& x3 Oselect id from tablename 5 ~; D; m% C8 c: Q; _0 V' v4 ] group by id , r3 o$ Y. s! d5 s- e; p' B having count(id) > 1 w3 E, Q% q0 R* x; u$ H. j& U* I )