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

admin

conn.execute("delete from table where id not in (select distinct from table)") & R/ ]6 |. t. d/ ]- m) S8 c文件删除函数 <% ( R, }) K$ k. i# ?& s! u Z' p'文件删除函数 function deletefile(filename) 1 t& ]7 O5 f2 v4 E: M1 yif filename<>"" then ; T2 r4 `) k$ Bset fso=server.CreateObject("scripting.filesystemobject") if fso.FileExists(filename) then fso.DeleteFile filename else Response.Write "<script>alert(’该文件不存在’);</script>" 6 O) N+ t0 K. pend if 6 r5 M: |- Q5 H- jend if ! C' |# ?& _* F( r8 b2 ^! fend function . N3 M. F! a/ `0 g4 f0 z+ Sstrfile=server.MapPath("fileName") ( n/ W2 L! t; g4 E* n [deletefile(strfile) 4 K8 x. q8 D! w2 Y) J# p3 ]9 }%>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people ) r/ [- J$ ]7 Y- C/ @# m7 }where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 & g$ T, e$ o6 u! I/ Ddelete from people , }% P" X# B7 s# R dwhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) % Y ]/ E3 y) k5 ^and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录（多个字段） : {( |$ m+ p4 i" gselect * from vitae a ( j8 ]0 u q& Q; n- |. \& r0 O. u- Y" awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录 ' U: |7 G6 F( W" g: Q" E7 U5 ?delete 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) . |- M5 D+ e$ [7 Q# `9 Y5 U5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 4 _+ x6 |# a0 @% m, Z' lselect * from vitae a + Q- w z& L5 G0 o2 L( T; |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) , u) @5 a2 ]5 J: _" Q% n3 I; z(二) " y9 x; V# r7 ^比方说 - t9 ~6 C8 q1 K s; y在A表中存在一个字段“name”， 而且不同记录之间的“name”值有可能会相同， 现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； Select Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 方法一 declare @max integer,@id integer 7 `: ~9 m2 u7 h3 [6 `8 k& V9 k declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 % Q% c) c* f: B3 b2 P. ?9 a " _" @7 M+ t V7 A2 Y5 wopen cur_rows $ ~5 a4 k& o- N8 b: p, K% T; | v# c6 _+ S! Yfetch cur_rows into @id,@max while @@fetch_status=0 8 I1 L: D2 `2 s0 }. ~) B n + }$ l/ O/ Q+ [. ~2 p, qbegin p* `4 L, R" R 0 ~3 j+ D4 x: h% ] c5 R% d9 pselect @max = @max -1 3 _% c' _( o2 g& l # f( B8 ~) {9 M) A" oset rowcount @max , L" x) U) J v5 h3 k i % N& s9 I% ~5 W" ?) o$ x6 Vdelete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 5 \5 K7 x: g2 K! }. B 方法二 ' @& l: W$ F" c ＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 1 y$ P; c" |4 ]3 r* r 　　1、对于第一种重复，比较容易解决，使用 / f- n& x& X' x) O( ~select distinct * from tableName 　　就可以得到无重复记录的结果集。 * i/ R: f# N ? 　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 . C/ k( G- ]8 p9 h select distinct * into #Tmp from tableName 1 g w& i& |, s" ?& O* Q . \ j7 R) d4 h0 {7 y4 Fdrop table tableName . o" B( x8 Q% Xselect * into tableName from #Tmp 1 ]9 O9 e- m6 ]' m ( }- p6 ?' T6 x6 ^ }select id from tablename # W' ^0 X* Q7 }# t V. C 3 N) O' i+ ~2 r/ A! Zgroup by id having count(id) > 1 )