查找重复数据

create table AAAA(  NAME VARCHAR2(10),  SEX  VARCHAR2(10),  AGE  VARCHAR2(10));

insert into AAAA(name,sex,age) values('1','1','1');insert into AAAA(name,sex,age) values('2','2','2');insert into AAAA(name,sex,age) values('2','2','2');insert into AAAA(name,sex,age) values('3','3','3');

--查找重复行：select *  from AAAA a where (a.NAME, a.SEX, a.AGE) in       (select NAME, SEX, AGE          from AAAA         group by NAME, SEX, AGE        having count(1) > 1);

--1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断select * from AAAAwhere NAME in (select  NAME  from  AAAA  group  by  NAME  having  count(NAME) > 1);

--2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录delete from AAAA where NAME  in (select  NAME  from AAAA  group  by  NAME   having  count(NAME) > 1)and rowid not in (select min(rowid) from  AAAA  group by NAME  having count(NAME )>1);

--3、查找表中多余的重复记录（多个字段）select * from AAAA awhere (a.NAME,a.SEX) in  (select NAME,SEX from AAAA group by NAME,SEX  having count(*) > 1);

--4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录delete from AAAA awhere (a.NAME,a.SEX) in  (select NAME,SEX from AAAA group by NAME,SEX having count(*) > 1)and rowid not in (select min(rowid) from AAAA group by NAME,SEX having count(*)>1);

--5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录select * from AAAA awhere (a.NAME,a.SEX) in  (select NAME,SEX from AAAA group by NAME,SEX having count(*) > 1)and rowid not in (select min(rowid) from AAAA group by NAME,SEX having count(*)>1);