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

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 % c3 ^: u# y' fResponse.Write "<script>alert(’该文件不存在’);</script>" end if ) J9 G9 C8 `* ]3 k' V Bend if 8 r2 \$ a3 h: ]2 A9 zend function : d: n# z8 [& E0 Q % c/ U% Y; L8 p4 zstrfile=server.MapPath("fileName") 0 d9 H1 u# u: h* ~' D, B8 ?4 |deletefile(strfile) 9 {/ U! ?& f: G& A0 U7 _% f%>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 5 y, y3 G2 C/ W; R& Ndelete from people 9 N8 I K' @0 J; j# Z- mwhere 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最小的记录 delete from vitae a 4 X/ r: B- h$ ^ jwhere (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最小的记录 select * 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) (二) 比方说 在A表中存在一个字段“name”， - W; Q `4 M5 l% j- ~ t( C: y6 L而且不同记录之间的“name”值有可能会相同， 2 X0 B, }& s7 Y现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； Select Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: 9 Q( ^* I' L* o+ fSelect Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 2 P: n9 u) H7 p) o6 M( x8 c' t方法一 - Z6 v3 }% Q) Q9 o$ B + p' h B) u E( c& {" I: T0 U5 X# u. Jdeclare @max integer,@id integer 4 K: W2 Y8 o3 H, Q( G( u2 M: H % [% U) B6 `; _; y# Pdeclare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 open cur_rows " z5 @5 f& o% _ 4 S$ N6 B! y* L* x& H9 ?( Jfetch cur_rows into @id,@max while @@fetch_status=0 begin 9 s# g" o+ x# f 6 ~- f- O) P3 b+ r% Zselect @max = @max -1 ! j9 b6 @' N/ T" r- Q5 fset rowcount @max 0 z4 T2 ~8 _4 Kdelete from 表名 where 主字段 = @id fetch cur_rows into @id,@max 2 K7 M& `) B [end 7 U4 j5 Y5 [, [4 y1 l$ R 0 Y9 K5 y' A1 x6 j' Z6 Nclose cur_rows set rowcount 0 2 X- F9 @: r( ~ ? w' H5 \ / w# i% g; b) e$ v! N: I ?方法二 ( A* v9 n8 x8 L9 Y3 C* D+ k 4 b$ v) j: [/ O$ C: ~＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 6 t" i3 J G* y' Q9 ~2 | 　　1、对于第一种重复，比较容易解决，使用 u% C; {9 V5 |' Jselect distinct * from tableName + j3 ]7 e ^+ N8 [' _: ]3 u　　就可以得到无重复记录的结果集。 . A+ ^3 l3 p, C' P6 ~! `$ e2 m * ^* @; T9 M1 {　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 + \; J) @4 ?: }1 f8 [- Y 0 H6 I. |. {: ^- `4 d% K) u8 Zselect distinct * into #Tmp from tableName % T, R; E& o" V6 O: pdrop table tableName 3 e6 N2 W! J: B : f* `; m5 y g6 B, o. Iselect * into tableName from #Tmp . v2 T- B- Y+ v. R select id from tablename 5 M1 B0 [, ~7 i) a8 \4 r 9 p3 U% n- g- P- K; K0 C' pgroup by id + \8 C# D5 l' n; E7 y; nhaving count(id) > 1 " @9 l$ v. e E8 ^ $ r5 H% l- w5 w9 B)