查找重复数据

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 AAAA
where 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 a
where (a.NAME,a.SEX) in  (select NAME,SEX from AAAA group by NAME,SEX  having count(*) > 1);

--4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from AAAA a
where (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 a
where (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);