Oracle查询重复数据与删除重复记录方法

Oracle查询重复数据与删除重复记录方法

比如现在有一人员表 (表名：peosons)
drop table  PERSONS;
create table PERSONS
(
  PNAME   VARCHAR2(50),
  CARDID VARCHAR2(18),
  ADDRESS VARCHAR2(100)   
);
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '张三', '430682199002121010', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '李四', '430682199002121010', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '王五', '430682199002121010', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '张三', '430682199002121010', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '赵六', '430682199002121011', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '赵六', '430682199002121011', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '小李子', '430682199002121011', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '小李子', '430682199002121012', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '小张子', '430682199002121013', '深圳');
insert into persons ( PNAME, CARDID, ADDRESS)
values ( '小张子', '430682199002121013', '深圳');
commit;
若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来
select p1.*
  from persons p1, persons p2
 where p1.rowid <> p2.rowid
   and p1.cardid = p2.cardid
   and p1.pname = p2.pname
   and p1.address = p2.address;

SQL> select p1.*
  2    from persons p1, persons p2
  3   where p1.rowid <> p2.rowid
  4     and p1.cardid = p2.cardid
  5     and p1.pname = p2.pname
  6     and p1.address = p2.address;

PNAME                CARDID             ADDRESS
-------------------- ------------------ --------------------
张三                 430682199002121010 深圳
张三                 430682199002121010 深圳
赵六                 430682199002121011 深圳
赵六                 430682199002121011 深圳
小张子               430682199002121013 深圳
小张子               430682199002121013 深圳

已选择6行。

可以实现上述效果。
　　几个删除重复记录的SQL语句

　　1.用rowid方法

　　2.用group by方法

　　3.用distinct方法

　　1.用rowid方法
    据据oracle带的rowid属性，进行判断，是否存在重复，语句如下：
select *
  from persons a
 where rowid != (select max(rowid) 　　from persons b
                  where a.pname = b.pname
                    and a.cardid = b.cardid
                    and a.address = b.address);
SQL> select *
  2    from persons a
  3   where rowid != (select max(rowid) 　　from persons b
  4                    where a.pname = b.pname
  5                      and a.cardid = b.cardid
  6                      and a.address = b.address);

PNAME                CARDID             ADDRESS
-------------------- ------------------ --------------------
张三                 430682199002121010 深圳
赵六                 430682199002121011 深圳
小张子               430682199002121013 深圳
删除重复数据，保留rowid最大值
delete from persons a
 where rowid != (select max(rowid) 　from persons b
                  where a.pname = b.pname
                    and a.cardid = b.cardid
                    and a.address = b.address);

2.group by方法
例:查询单个字符重复
select count(pname) ， max(pname)
  from persons --列出重复的记录数，并列出他的name属性
 group by pname -- --按panme分组后找出表中pname列重复，即出现次数大于一次
having count(*) > 1
SQL> select count(pname) ， max(pname)
  2    from persons
  3   group by pname
  4  having count(*) > 1;

COUNT(PNAME) MAX(PNAME)
------------ --------------------------------------------------
           2 赵六
           2 小张子
           2 小李子
           2 张三
删除数据
delete from persons
 where pname in
       (select pname from persons group by pname having count(*) > 1);

例:查询多个字段重复
SELECT *
  FROM PERSONS A 　　
 WHERE (A.PNAME, A.CARDID, A.ADDRESS) IN
       (SELECT PNAME, CARDID, ADDRESS
          FROM PERSONS
         GROUP BY PNAME, CARDID, ADDRESS
        HAVING COUNT(*) > 1);
删除表中多余的重复记录(多个字段)，只留有rowid最小的记录
DELETE FROM PERSONS A 　　
 WHERE (A.PNAME, A.CARDID, A.ADDRESS) IN
       (SELECT PNAME, CARDID, ADDRESS
          FROM PERSONS
         GROUP BY PNAME, CARDID, ADDRESS
        HAVING COUNT(*) > 1) 　AND
 ROWID NOT IN (SELECT MIN(ROWID)
                       FROM PERSONS                     
                      GROUP BY PNAME, CARDID, ADDRESS
                     HAVING COUNT(*) > 1);
查询表中多余的重复记录(多个字段)，不包含rowid最小的记录
SELECT * FROM PERSONS A 　　
 WHERE (A.PNAME, A.CARDID, A.ADDRESS) IN
       (SELECT PNAME, CARDID, ADDRESS
          FROM PERSONS
         GROUP BY PNAME, CARDID, ADDRESS
        HAVING COUNT(*) > 1) 　AND
 ROWID NOT IN (SELECT MIN(ROWID)
                       FROM PERSONS                     
                      GROUP BY PNAME, CARDID, ADDRESS
                     HAVING COUNT(*) > 1);
SQL> SELECT * FROM PERSONS A 　　
  2   WHERE (A.PNAME, A.CARDID, A.ADDRESS) IN
  3         (SELECT PNAME, CARDID, ADDRESS
  4            FROM PERSONS
  5           GROUP BY PNAME, CARDID, ADDRESS
  6          HAVING COUNT(*) > 1) 　AND
  7   ROWID NOT IN (SELECT MIN(ROWID)
  8                         FROM PERSONS
  9                        GROUP BY PNAME, CARDID, ADDRESS
 10                       HAVING COUNT(*) > 1);

PNAME                CARDID               ADDRESS
-------------------- -------------------- --------------------
小张子               430682199002121013   深圳
赵六                 430682199002121011   深圳
小李子               430682199002121011   深圳
赵六                 430682199002121011   深圳
小李子               430682199002121012   深圳
小张子               430682199002121013   深圳
张三                 430682199002121010   深圳
张三                 430682199002121010   深圳
王五                 430682199002121010   深圳
李四                 430682199002121010   深圳

已选择10行。