ORACLE 去除重复记录的方法

select count (*) from a where name in ( --统计A表中 name不重复的个数
       select name from a group by name having count(*)=1 -- 查询出不重复的table A的 name字段值
);

-- 查询表 a 的 name字段重复的记录
select *
  from a
 where a.rowid > (select min(x.rowid) from a x where x.name = a.name);

create table t_person(
       cardid integer primary key,
       pname varchar2(10),
       address varchar2(20)
) tablespace cici;

表结构建立

插入如下数据

select rowid ,p.* from t_person p order by cardid asc

1. 子查询 查询重复的记录

•  查询单个字段（cardid）重复的记录

 --定位查询
select *
  from t_person
 where PNAME in
      --获取重复记录的pname属性
       (select PNAME from t_person  group by PNAME having count(PNAME) > 1)

•  查询多个字段来(cardid,pname)重复的记录

select *
  from t_person  a
 where (a.pname, a.ADDRESS) in
       (select pname, ADDRESS
          from t_person
         group by pname, ADDRESS
        having count(*) > 1)

--不包含rowid最小的记录
select *
  from t_person a
 where (a.pname, a.ADDRESS) in
       (select pname, ADDRESS
          from t_person
         group by pname, ADDRESS
        having count(*) > 1)
and rowid not in (select min(rowid) from t_person  group by cardid,ADDRESS having count(*)>1)

2. 查询 非重复的记录

•  pname不重复的数据记录

select *
  from t_person
 where PNAME not in
      --获取重复记录的pname属性
       (select PNAME from t_person group by PNAME having count(PNAME) > 1)

 

• address不重复的数据

select *
  from t_person
 where address not in
      --获取重复记录的pname属性
       (select address from t_person group by address having count(address) > 1) order by cardid asc

• 查询pname 和 address都不重复的数据

 

select *
  from t_person
 where PNAME not in
      --获取重复记录的pname属性
       (select PNAME from t_person  group by PNAME having count(PNAME) > 1)  
 intersect /*求交集*/
select *
  from t_person
 where address not in
      --获取重复记录的pname属性
       (select address from t_person group by address having count(address) > 1 )

 

      4. 删除 子查询查找出的重复记录数目

•    查询多个字段来(cardid,pname)重复的记录

--不包含rowid最小的记录
delete from t_person
where cardid in (
      select cardid
    from t_person a
 where (a.pname, a.ADDRESS) in
       (select pname, ADDRESS
          from t_person
         group by pname, ADDRESS
        having count(*) > 1)
and rowid not in (select min(rowid) from t_person group by cardid,ADDRESS having count(*)>1))