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

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

Oracle查詢重複資料與重複資料刪除記錄方法