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

admin

conn.execute("delete from table where id not in (select distinct from table)") 文件删除函数 <% ' u' x# f" M" i2 v' Q7 H- s0 \) t3 h'文件删除函数 function deletefile(filename) if filename<>"" then set fso=server.CreateObject("scripting.filesystemobject") $ Y5 d7 m+ @+ H- G# q, J* Tif fso.FileExists(filename) then fso.DeleteFile filename * B/ r$ q# \; n" J1 E; ~+ G+ C0 selse , g9 d' j9 I( u! G, h Y5 Q" XResponse.Write "<script>alert(’该文件不存在’);</script>" end if end if 1 L% p8 ] n- j I7 _* i" v: c; send function , _7 I' `0 Y; |) i- R4 J - N( P! \% j4 ]strfile=server.MapPath("fileName") deletefile(strfile) + l$ K: `& C9 `" a$ V$ D1 I8 N%>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people ) Z) {: Z( u' p( ]where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 * z& P Y3 g: E8 T Ydelete 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、查找表中多余的重复记录（多个字段） `5 J) A; b% y! p' }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 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) + w: x, p6 }# L8 w9 N# 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) * \5 s) z9 s/ a# \8 dand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) ( \- n4 a9 O x5 h(二) 比方说 + J% g J) A# a% g; L8 f' c8 R0 g在A表中存在一个字段“name”， 而且不同记录之间的“name”值有可能会相同， 6 n; [, j( i ]" }; z现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； , h! S$ C4 K* l; JSelect Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 方法一 ! k1 ]. T2 b* ? # M& \! B* F- s |3 E0 z( Bdeclare @max integer,@id integer 3 _- |; w# b: F9 ?6 h& s% jdeclare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 : d% _ R9 X4 I0 F$ u% o( C5 H3 ~# `open cur_rows 2 W5 M; S5 i ?5 p" P% e& Jfetch cur_rows into @id,@max 7 P! ^) {4 d- Q- l- L3 @ while @@fetch_status=0 8 y; o) I: q" d4 K+ }1 L; O1 C begin select @max = @max -1 0 I$ w& v# h) I, ^7 A' ]set rowcount @max " Q. d# ~# i5 t& a% T9 g delete from 表名 where 主字段 = @id $ Z* c3 W4 e! l* N9 U* l % S5 t" H; O5 _$ ^1 Wfetch cur_rows into @id,@max end & E+ R3 }' Z3 F8 t iclose cur_rows 0 g" X/ O. T' Y4 n b' k ( f$ _/ a4 y' N6 P' e( Rset rowcount 0 $ F8 Q1 B7 X7 i- a& ]' @& K 6 W5 i3 D3 q4 ~3 U8 P方法二 ) o4 p' U3 L; g) `" x/ y 3 k. Z. ^7 {3 `＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 　　1、对于第一种重复，比较容易解决，使用 select distinct * from tableName 　　就可以得到无重复记录的结果集。 　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 % M" V' |3 y5 ~' V* @% g# e select distinct * into #Tmp from tableName drop table tableName & J$ Z1 Y6 U! f5 @ ; R) V; j7 @; C0 |8 M- Iselect * into tableName from #Tmp 0 `* z5 ~* j( f: n select id from tablename 9 d6 O& C) n# ygroup by id having count(id) > 1 )