删除数据库中的重复记录

删除数据库中的重复记录 比如现在有一人员表 表名peosons 若想将姓名、身份证号、住址这三个字段完全相 同的记录查询出来 select p1. from persons p1persons p2 where p1.idp2.id and p1.cardid p2.cardid and p1.pname p2.pname and p1.address p2.address 可以实现 上述效果 几个删除重复记录的SQL语句 1.用rowid方法 2.用group by方法 3.用 distinct方法 1。用rowid方法 据据oracle带的rowid属性进行判断是否存在重复语句 如下 查数据: select from table1 a where rowid select maxrowid from table1 b where a.name1b.name1 and a.name2b.name2...... 删数据 delete from table1 a where rowid select maxrowid from table1 b where a.name1b.name1 and a.name2b.name2...... 2.group by方法 查数据: select countnum maxname from student --列出重复的记录数并列出他 的name属性 group by num having countnum 1 --按num分组后找出表中num列重复即 出现次数大于一次 删数据 delete from student group by num having countnum 1 这 样的话就把所有重复的都删除了。 3.用distinct方法 -对于小的表比较有用 create table table_new as select distinct from table1 minux truncate table table1 insert into table1 select from table_new 查询及删除重复记录的方法大全 1、查找表中多余的 重复记录重复记录是根据单个字段peopleId来判断 select from people where peopleId in select peopleId from people group by peopleId having countpeopleId 1 2、 删除表中多余的重复记录重复记录是根据单个字段peopleId来判断只留有rowid最小 的记录 delete from people where peopleId in select peopleId from people group by peopleId having countpeopleId 1 and rowid not in select minrowid from people group by peopleId having countpeopleId 1 3、查找表中多余的重复记录多个字段 select from vitae a where a.peopleIda.seq in select peopleIdseq from vitae group by peopleIdseq having count 1 4、删除表中多余的重复记录多个字段只留有rowid最小的记录 delete from vitae a where a.peopleIda.seq in select peopleIdseq from vitae group by peopleIdseq having count 1 and rowid not in select minrowid from vitae group by peopleIdseq having count1 5、查找表中多余的重复记录多个字段不包含rowid最小的 记录 select from vitae a where a.peopleIda.seq in select peopleIdseq from vitae group by peopleIdseq having count 1 and rowid not in select minrowid from vitae group by peopleIdseq having count1 二 比方说 在A表中存在一个字段“name” 而且不同记录 之间的“name”值有可能会相同 现在就是需要查询出在该表中的各记录之间“name” 值存在重复的项 Select NameCount From A Group By Name Having Count 1 如果 还查性别也相同大则如下: Select NamesexCount From A Group By Namesex Having Count 1 三 方法一 declare max integerid integer declare cur_rows cursor local for select 主字段count from 表名 group by 主字段 having count 1 open cur_rows fetch cur_rows into idmax while fetch_status0 begin select max max -1 set rowcount max delete from 表名 where 主字段 id fetch cur_rows into idmax end close cur_rows set rowcount 0 方法二 重复记录有两个意义上的重复记录一是完全重复的记录也即所 有字段均重复的记录二是部分关键字段重复的记录 比如Name字段重复而其他字段 不一定重复或都重复可以忽略。 1、对于第一种重复比较容易解决使用 select distinct from tableName就可以得到无重复记录的结果集。 如果该表需要删除重复的记录重 复记录保留1条可以按以下方法删除 select distinct into Tmp from tableName drop table tableName select into tableName from Tmp drop table Tmp 发生这种重复的原 因是表设计不周产生的增加唯一索引列即可解决。 2、这类重复问题通常要求保留 重复记录中的第一条记录操作方法如下 假设有重复的字段为NameAddress要求得 到这两个字段唯一的结果集 select identityint11 as autoID into Tmp from tableName select minautoID as autoID into Tmp2 from Tmp group by NameautoID select from Tmp where autoID inselect autoID from tmp2 最后一个select即得到了NameAddress不 重复的结果集但多了一个autoID字段实际写时可以写在select子句中省去此列 四 查询重复 select from tablename where id in select id from tablename group by id having countid 1