1、比如现在有一人员表 （表名：peosons）若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来 select p1.* from persons p1,persons p2 where p1.id1 -按 num 分组后找出表中 num 列重复，即出现次数大于一次 删数据：delete from student group by num having count(num) 1这样的话就把所有重复的都删除了。 3.用 distinct 方法 -对于小的表比较有用 create table table_new as select distinct * from table1 minux t
2、runcate table table1;insert into table1 select * from table_new;查询及删除重复记录的方法大全1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid 最小的记录delete from people w
3、here 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 awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,s
4、eq having count(*) 1)4、删除表中多余的重复记录（多个字段），只留有 rowid 最小的记录 delete from vitae awhere (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 最小的记录sele
5、ct * from vitae awhere (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”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；Select Name,Cou
6、nt(*) From A Group By Name Having Count(*) 1 如果还查性别也相同大则如下:Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) 1 (三)方法一 declare max integer,id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) ； 1 open cur_rows fetch cur_rows into id,max
7、 while fetch_status=0 begin select max = max -1 set rowcount max delete from 表名 where 主字段 = id fetch cur_rows into id,max end close cur_rows set rowcount 0方法二 重复记录有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如 Name 字段重复，而其他字段不一定重复或都重复可以忽略。 1、对于第一种重复，比较容易解决，使用 select distinct * from tableName 就可
8、以得到无重复记录的结果集。 如果该表需要删除重复的记录（重复记录保留 1 条），可以按以下方法删除 select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table#Tmp 发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下 假设有重复的字段为 Name,Address，要求得到这两个字段唯一的结果集 select identity(int,1,1) as autoID
9、, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2) 最后一个 select 即得到了 Name，Address 不重复的结果集（但多了一个autoID 字段，实际写时可以写在 select 子句中省去此列） (四)查询重复 select * from tablename where id in ( select id from tablename group by id having count(id) 1 )
