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

admin

conn.execute("delete from table where id not in (select distinct from table)") 文件删除函数 <% '文件删除函数 function deletefile(filename) 3 ^! V! W' s( S m$ d% _if filename<>"" then 3 }7 j" [# R" @# y8 v' [set fso=server.CreateObject("scripting.filesystemobject") if fso.FileExists(filename) then 9 Q, b/ h- M4 k$ ?+ I2 qfso.DeleteFile filename 1 b) K6 q, E( M. j2 ]" Relse Response.Write "<script>alert(’该文件不存在’);</script>" end if 3 {) i. `* u/ r6 O/ ^# Dend if end function strfile=server.MapPath("fileName") deletefile(strfile) %>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 0 z7 _" b' N; V7 }4 |* A9 \6 [select * from people 8 F7 g- e+ ~2 J' d( ewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 & i: }9 c' G0 ~7 u1 N7 ~( edelete 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最小的记录 . g. r6 q# r a3 Q" Y- Y$ Edelete from vitae a & t! }8 K4 ^* X* c- owhere (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最小的记录 : W& {8 b+ ~3 p1 B! b# ^. y5 o- Zselect * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) & Z* @2 s0 R( e# oand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) (二) / K3 o; r/ L+ I2 w+ y比方说 在A表中存在一个字段“name”， . t) i$ `2 s3 B z' S, M j, f而且不同记录之间的“name”值有可能会相同， 现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； 2 i; S4 g4 \( ~: TSelect Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) ! {( ?% g; B5 s6 G, _$ N2 q0 |方法一 2 r$ ?0 H# }2 z3 t1 v 0 _2 o, ?3 M& m& ?$ n7 Hdeclare @max integer,@id integer 1 ?- G9 g# v `& z7 m: J& S- W% s' [! o declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 % y0 U3 U9 q1 C0 Qopen cur_rows 3 E% }" t5 Z7 w2 y fetch cur_rows into @id,@max 7 K0 Z6 M. W/ c; W5 W4 Wwhile @@fetch_status=0 begin select @max = @max -1 1 C2 l8 R9 p1 X set rowcount @max 0 k t5 N/ ?. V: T5 l8 f1 X. Jdelete from 表名 where 主字段 = @id 8 G8 T8 N& N8 A8 C. ^* Z + [7 \# ~1 d' _& nfetch cur_rows into @id,@max 3 U* D9 K# W. e8 D0 h) S) Kend close cur_rows . \; M, O/ F) w7 m9 n; _* M set rowcount 0 : _( B, m' r! M, G2 ]0 J 方法二 # B- J/ Y/ H( A% P ＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 3 N* y# k/ k T/ j7 v2 L2 K 　　1、对于第一种重复，比较容易解决，使用 * G' [6 V4 K! f5 w5 qselect distinct * from tableName : ^3 R# y1 X5 ` 　　就可以得到无重复记录的结果集。 　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 4 S/ m+ j# j& `' i& h select distinct * into #Tmp from tableName $ K' b5 {0 C+ r. o% k+ Idrop table tableName + t4 ?6 s1 L4 j; `1 [" w. p8 a 8 M$ Z0 [1 |1 V# p$ M7 v- V( U# |select * into tableName from #Tmp $ |' y# m3 t- [' l* A! U- Fselect id from tablename group by id 6 d2 A$ z$ G+ S4 ~, W; L7 ~ 0 U( T$ z2 `4 x) r- Q& I+ Phaving count(id) > 1 ) h) t+ Y; c3 h: c) @) O& L / r% E6 q0 B% R, N* P b: B, @)