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

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(*) > 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行。

 

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