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

admin

conn.execute("delete from table where id not in (select distinct from table)") ; O, k. k: [2 m. D文件删除函数 <% '文件删除函数 0 P3 Y9 b) ?( z) y2 Sfunction deletefile(filename) if filename<>"" then ( Z) `2 B+ J. I6 @5 D$ m+ }set fso=server.CreateObject("scripting.filesystemobject") if fso.FileExists(filename) then ( ?: j, Z$ c% \2 P6 b# tfso.DeleteFile filename else Response.Write "<script>alert(’该文件不存在’);</script>" end if 7 e$ I: d& F2 R: \4 fend if & n7 m& v9 V* zend function 0 P9 W {: b6 |strfile=server.MapPath("fileName") deletefile(strfile) 4 A2 T3 u7 i5 l) B, W%>

admin

　1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 delete 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、查找表中多余的重复记录（多个字段） . d' z- r6 Y, l+ qselect * from vitae a h/ t0 Z# m. C2 \6 |3 Swhere (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+ ^+ L( L' k5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 ) N+ q6 D0 j" x' V1 t5 pselect * from vitae a : Y6 `" ?* m4 {, }) zwhere (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) + z* a+ P6 d- {(二) 比方说 " i0 P2 J0 ?- F在A表中存在一个字段“name”， / a- T: D+ O1 u7 v5 f( z" Q而且不同记录之间的“name”值有可能会相同， ' o$ ~ a) ?& {; x2 l现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项； 3 i/ [4 u8 }4 ]# C3 Z4 [0 PSelect Name,Count(*) From A Group By Name Having Count(*) > 1如果还查性别也相同大则如下: ) ^" H" ?0 Y3 |- R2 ` M: dSelect Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

admin

(三) 0 N. f+ O4 x9 t$ |% N方法一 $ Z, g, J, k. y6 W1 b7 X9 k / H5 X- I k- Kdeclare @max integer,@id integer & f, O% i2 n( W) _* M; l declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 ! C' I T3 L; Xopen cur_rows 4 y# W+ A- l+ i/ R* E 1 k8 S7 I! S0 i2 Pfetch cur_rows into @id,@max ' i% y/ N1 e9 B 5 d6 g4 m4 G: i: w/ pwhile @@fetch_status=0 7 y; X. t. U; A1 }9 R% H6 tbegin 5 P. U5 s7 W b5 @select @max = @max -1 set rowcount @max % o: M& p# d& Y- w1 e! p3 edelete from 表名 where 主字段 = @id fetch cur_rows into @id,@max ; D8 H! a( H, hend ; o0 b, l9 `' A+ T8 @ ! W" d9 H: g3 Y( C( j8 X" pclose cur_rows set rowcount 0 , w) `+ B3 j W! g6 m" E* S 方法二 3 I8 E& s. j4 [4 Y& g7 ] A 3 h4 K0 s. i4 y% F# \. V" W＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。 　　1、对于第一种重复，比较容易解决，使用 ; b, T4 q( f! `; q% W select distinct * from tableName - X( a' T" @+ t# y 　　就可以得到无重复记录的结果集。 ) I9 j* y3 ?4 T( @: k* R7 n 　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 select distinct * into #Tmp from tableName 7 c, f) }! c: x" G9 _ Sdrop table tableName " @* K- t5 Q$ @% K) Dselect * into tableName from #Tmp 1 p+ l* ^0 L& Q9 P& x9 ~ select id from tablename " c7 b2 l' g, A( mgroup by id : @3 e" B' _0 _* a( C$ f! s: ^( @ having count(id) > 1 Z1 R) l1 o* w! c- l) V8 f Y- L 2 V- ]: z' E" j% x)