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





比如现在有一人员表 (表名：peosons)
drop table  PERSONS;
create