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);
例:查詢多個字段重復
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行。
