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

admin

conn.execute("delete from table where id not in (select distinct from table)") 文件删除函数 <% '文件删除函数 * O. f* V g/ Q9 zfunction deletefile(filename) if filename<>"" then set fso=server.CreateObject("scripting.filesystemobject") - v5 d* Y9 W7 Y" Fif fso.FileExists(filename) then 4 `% C; e. i. M$ Pfso.DeleteFile filename ' R! }* t; c1 N0 v2 {5 b% u' jelse % K: E6 S9 A3 R6 f. b" O: CResponse.Write "<script>alert(’该文件不存在’);</script>" end if end if end function ( T- `) D* z2 A5 Q4 @# h strfile=server.MapPath("fileName") deletefile(strfile) %>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 ; X. r: K8 ~. E/ G; B8 Q/ U6 adelete 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、查找表中多余的重复记录（多个字段） " Z: Z' E( {! `' Aselect * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录 ! C8 U) F8 |2 W$ O, z/ K: ]1 x' Cdelete from vitae a % d" P4 ]2 y1 y) R4 uwhere (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) " \; r, ?0 Y. `# b/ ~5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) ( t5 G( q" B% Hand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) . b& A$ s1 [# ^" a* u' M* O(二) 0 ]/ B: u! Z# K( D! [( W比方说 ' _$ u* |3 l% S5 U O. k在A表中存在一个字段“name”， 而且不同记录之间的“name”值有可能会相同， 现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； Select Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: 3 e; Z) V9 U+ O; q1 I7 F5 e6 y vSelect Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 方法一 declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 open cur_rows 6 _6 w/ k! n! ?( u6 d/ f fetch cur_rows into @id,@max while @@fetch_status=0 begin 6 ~$ k0 Q4 ?7 o* Q select @max = @max -1 set rowcount @max A( p: s) R1 B; \- _5 `0 U 2 _! T: i9 C8 Y. J6 gdelete from 表名 where 主字段 = @id fetch cur_rows into @id,@max 4 M S: Y1 T+ n" @( y8 ?9 pend ) Z7 K; G" I. [/ r close cur_rows ( A1 M' C2 @" V1 H7 F( I$ W 2 z s1 G9 g- {; ~& l, B) D0 V1 Bset rowcount 0 ( a! S) |) I7 A7 a ; V7 u% N0 h$ ~方法二 8 P3 q9 {2 {$ N4 j 1 t7 i& Y! d: Y0 C& J9 A＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 3 C" C5 E% c: Z; ~　　1、对于第一种重复，比较容易解决，使用 select distinct * from tableName 2 n6 B+ D0 L) T* N7 k) f 　　就可以得到无重复记录的结果集。 ; A& }5 Z, G) x; U　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 select distinct * into #Tmp from tableName ; l6 W0 f/ n9 c# t ! G2 e% O9 D- sdrop table tableName + y4 q, N! i& P C/ R0 c' j2 F1 `/ b select * into tableName from #Tmp 6 w& R, c0 L( Z& D* J8 I* ~ 0 P# t. \! r& d4 R. ~6 sselect id from tablename 5 m0 x- W3 M$ @: q; C, `3 z: | 3 Q1 z" A& z* i' N3 R [group by id 9 |" p( o8 @8 U% m having count(id) > 1 " l$ [7 _% ]7 u5 g+ t& j- H # B5 K2 ]9 K# ]' n7 E)