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);

版权声明：本文为博主原创文章，未经博主允许不得转载。

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

标签：