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

来源：互联网发布：mysql数据库免安装版编辑：程序博客网时间：2024/05/17 08:21
比如现在有一人员表 (表名：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(*) > 1SQL> 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行。
查询数据不包含重复数据
思想：
distinct去重，只能显示去重的字段
select distinct pname,cardid,address from persons
思想:
重复字段:通过group by 去重，如果要显示其他这段，使用函数max(字段名)或min(字段)
select pname, cardid, address from persons group by pname, cardid, address
说明:本人于ITEYE创建于2014年,现转移到CSDN
0 0