在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

Oracle 如何查找并刪除重復(fù)記錄

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        創(chuàng)建示例表
        使用聚合函數(shù)查找重復(fù)記錄
            基于單個(gè)字段的重復(fù)記錄
            基于多個(gè)字段的重復(fù)記錄
        使用分析函數(shù)查找重復(fù)記錄
            基于單個(gè)字段的重復(fù)記錄
            基于多個(gè)字段的重復(fù)記錄
        刪除重復(fù)記錄
            使用子查詢刪除重復(fù)記錄
            使用分析函數(shù)刪除重復(fù)記錄
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。由于一些歷史原因或者人為的誤操作，可能會(huì)導(dǎo)致數(shù)據(jù)表中存在重復(fù)的記錄。前文介紹了如何查找 MySQL 中的重復(fù)數(shù)據(jù)以及如何刪除這些重復(fù)的記錄，今天我們繼續(xù)討論如何在 Oracle 數(shù)據(jù)庫中實(shí)現(xiàn)相同的功能。
創(chuàng)建示例表

我們首先創(chuàng)建一個(gè)示例表 people 并生成一些數(shù)據(jù)：

CREATE TABLE people (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar2(50) NOT NULL,
    email varchar2(100) NOT NULL
);

INSERT INTO people(name, email)
VALUES ('張三', 'zhangsan@test.com');
INSERT INTO people(name, email)
VALUES ('李四', 'lisi@test.com');
INSERT INTO people(name, email)
VALUES ('王五', 'wangwu@test.com');
INSERT INTO people(name, email)
VALUES ('李斯', 'lisi@test.com');
INSERT INTO people(name, email)
VALUES ('王五', 'wangwu@test.com');
INSERT INTO people(name, email)
VALUES ('王五', 'wangwu@test.com');

SELECT * FROM people;
ID|NAME |EMAIL            |
--|-----|-----------------|
 1|張三  |zhangsan@test.com|
 2|李四  |lisi@test.com    |
 3|王五  |wangwu@test.com  |
 4|李斯  |lisi@test.com    |
 5|王五  |wangwu@test.com  |
 6|王五  |wangwu@test.com  |



其中，2 和 4 的 email 字段存在重復(fù)數(shù)據(jù)；3、5 和 6 的 name 和 email 字段存在重復(fù)數(shù)據(jù)。

此時(shí)，如果我們想要為 email 字段創(chuàng)建一個(gè)唯一約束，將會(huì)返回錯(cuò)誤：

ALTER TABLE people ADD CONSTRAINT uk_people_email UNIQUE (email);
ERROR at line 1:
ORA-02299: cannot validate (TONY.UK_PEOPLE_EMAIL) - duplicate keys found



顯然，我們必須找出并刪除 email 字段中的重復(fù)記錄才能創(chuàng)建唯一約束。
使用聚合函數(shù)查找重復(fù)記錄
基于單個(gè)字段的重復(fù)記錄

如果想要找出 email 重復(fù)的數(shù)據(jù)，可以基于該字段進(jìn)行分組統(tǒng)計(jì)，并且返回行數(shù)大于 1 的分組：

SELECT email, count(email)
FROM people
GROUP BY email
HAVING count(email) > 1;
EMAIL          |COUNT(EMAIL)|
---------------|------------|
lisi@test.com  |           2|
wangwu@test.com|           3|



查詢結(jié)果顯示有兩個(gè)郵箱地址存在重復(fù)情況。如果想要查看完整的重復(fù)數(shù)據(jù)，可以使用子查詢或者連接查詢：

SELECT *
FROM people
WHERE email IN (
      SELECT email
      FROM people
      GROUP BY email
      HAVING count(email) > 1)
ORDER BY email;
ID|NAME |EMAIL          |
--|-----|---------------|
 2|李四  |lisi@test.com  |
 4|李斯  |lisi@test.com  |
 3|王五  |wangwu@test.com|
 5|王五  |wangwu@test.com|
 6|王五  |wangwu@test.com|

WITH d AS (
  SELECT email
  FROM people
  GROUP BY email
  HAVING count(email) > 1)
SELECT p.*
FROM people p
JOIN d ON (d.email = p.email)
ORDER BY p.email;
ID|NAME |EMAIL          |
--|-----|---------------|
 2|李四  |lisi@test.com  |
 4|李斯  |lisi@test.com  |
 3|王五  |wangwu@test.com|
 5|王五  |wangwu@test.com|
 6|王五  |wangwu@test.com|



另一種查找重復(fù)記錄的方法就是直接使用自連接查詢和 distinct 操作符，例如：

SELECT DISTINCT p.*
FROM people p
JOIN people d ON p.email = d.email
WHERE p.id <> d.id
ORDER BY p.email;
ID|NAME |EMAIL          |
--|-----|---------------|
 2|李四  |lisi@test.com  |
 4|李斯  |lisi@test.com  |
 3|王五  |wangwu@test.com|
 5|王五  |wangwu@test.com|
 6|王五  |wangwu@test.com|



注意不能省略 distinct，否則會(huì)某些數(shù)據(jù)（3、5、6）會(huì)返回多次。
基于多個(gè)字段的重復(fù)記錄

如果我們想要找出 name 和 email 兩個(gè)字段都重復(fù)的數(shù)據(jù)，可以基于這兩個(gè)字段進(jìn)行分組統(tǒng)計(jì)：

SELECT *
FROM people
WHERE (name, email) IN (
      SELECT name, email
      FROM people
      GROUP BY name, email
      HAVING count(1) > 1)
ORDER BY email;
ID|NAME |EMAIL          |
--|-----|---------------|
 3|王五  |wangwu@test.com|
 5|王五  |wangwu@test.com|
 6|王五  |wangwu@test.com|

WITH d AS (
  SELECT name, email
  FROM people
  GROUP BY name, email
  HAVING count(*) > 1)
SELECT p.*
FROM people p
JOIN d ON (d.name = p.name AND d.email = p.email)
ORDER BY p.email;
ID|NAME |EMAIL          |
--|-----|---------------|
 3|王五  |wangwu@test.com|
 5|王五  |wangwu@test.com|
 6|王五  |wangwu@test.com|



只有當(dāng) name 和 email 都相同時(shí)才是重復(fù)數(shù)據(jù)，所以 2 和 4 不是重復(fù)記錄。
使用分析函數(shù)查找重復(fù)記錄

使用聚合函數(shù)查找重復(fù)記錄需要掃描同一個(gè)表兩次，如果表中的數(shù)據(jù)量很大時(shí)，可能存在性能問題。為此，我們可以采用另一種方法：分析函數(shù)（窗口函數(shù)）。
基于單個(gè)字段的重復(fù)記錄

首先，我們通過 count() 分析函數(shù)找出每個(gè) email 出現(xiàn)的次數(shù)：

SELECT id, name, email, count(*) over (partition by email) cnt
FROM people;
ID|NAME |EMAIL            |CNT|
--|-----|-----------------|---|
 4|李斯  |lisi@test.com    |  2|
 2|李四  |lisi@test.com    |  2|
 6|王五  |wangwu@test.com  |  3|
 3|王五  |wangwu@test.com  |  3|
 5|王五  |wangwu@test.com  |  3|
 1|張三  |zhangsan@test.com|  1|



分析函數(shù)不僅可以找出字段的重復(fù)次數(shù)，同時(shí)還可以保留原表中的數(shù)據(jù)，避免了二次掃描的操作。接下來我們只需要返回次數(shù)大于 1 的記錄即可：

WITH d AS (
  SELECT id, name, email,
         count(*) over (partition by email) cnt
  FROM people)
SELECT *
FROM d
WHERE cnt > 1
ORDER BY id;
ID|NAME |EMAIL          |CNT|
--|-----|---------------|---|
 2|李四  |lisi@test.com  |  2|
 3|王五  |wangwu@test.com|  3|
 4|李斯  |lisi@test.com  |  2|
 5|王五  |wangwu@test.com|  3|
 6|王五  |wangwu@test.com|  3|



基于多個(gè)字段的重復(fù)記錄

分析函數(shù)同樣支持基于多個(gè)字段的分區(qū)操作，以下語句可以用于找出 name 和 email 兩個(gè)字段都重復(fù)的數(shù)據(jù)：

WITH d AS (
  SELECT id, name, email,
         count(*) over (partition by name, email) cnt
  FROM people)
SELECT *
FROM d
WHERE cnt > 1
ORDER BY id;
ID|NAME |EMAIL          |CNT|
--|-----|---------------|---|
 3|王五  |wangwu@test.com|  3|
 5|王五  |wangwu@test.com|  3|
 6|王五  |wangwu@test.com|  3|



顯然，分析函數(shù)不但擁有更好的性能，也具有更好的可讀性。
刪除重復(fù)記錄

記下來需要解決的問題就是如何刪除這些重復(fù)記錄，通常我們需要保留其中的一條記錄。
使用子查詢刪除重復(fù)記錄

假如我們想要?jiǎng)h除 email 字段重復(fù)的記錄，只保留其中 id 最大的一條；可以使用子查詢找出需要保留的數(shù)據(jù)，然后刪除其他的數(shù)據(jù)：

DELETE
FROM people
WHERE id NOT IN (
      SELECT max(id)
      FROM people
      GROUP BY email
     );



刪除之后再次查詢 people 表：

SELECT * FROM people;
ID|NAME |EMAIL            |
--|-----|-----------------|
 1|張三  |zhangsan@test.com|
 4|李斯  |lisi@test.com    |
 6|王五  |wangwu@test.com  |



也可以使用關(guān)聯(lián)子查詢刪除重復(fù)的數(shù)據(jù)：

DELETE
FROM people p
WHERE p.id NOT IN (
      SELECT max(id)
      FROM people
      WHERE email = p.email
     );



在執(zhí)行上面的語句之前，記得重新創(chuàng)建 people 表并生成測試數(shù)據(jù)。

    ??想一想，如果想要保留重復(fù)數(shù)據(jù)中 id 最小的數(shù)據(jù)應(yīng)該怎么實(shí)現(xiàn)呢？

使用分析函數(shù)刪除重復(fù)記錄

ROW_NUMBER() 分析函數(shù)可以用于將數(shù)據(jù)進(jìn)行分組，然后為每一條數(shù)據(jù)分配一個(gè)唯一的數(shù)字編號(hào)。例如：

SELECT id, name, email,
       row_number() over (PARTITION BY email ORDER BY id) AS row_num
FROM people;
ID|NAME|EMAIL             |ROW_NUM|
--|----|------------------|-------|
 2|李四  |lisi@test.com    |      1|
 4|李斯  |lisi@test.com    |      2|
 3|王五  |wangwu@test.com  |      1|
 5|王五  |wangwu@test.com  |      2|
 6|王五  |wangwu@test.com  |      3|
 1|張三  |zhangsan@test.com|      1|



以上語句基于 email 分組（PARTITION BY email），同時(shí)按照 id 進(jìn)行排序（ORDER BY id），然后為每個(gè)組內(nèi)的數(shù)據(jù)分配一個(gè)編號(hào)；如果編號(hào)大于 1 就意味著存在重復(fù)的數(shù)據(jù)。

    ??除了 ROW_NUMBER() 之外，RANK() 或者 DENSE_RANK() 函數(shù)也可以實(shí)現(xiàn)以上功能。關(guān)于窗口函數(shù)的介紹和使用案例，可以參考這篇文章。

我們可以基于該查詢結(jié)果刪除重復(fù)的記錄：

DELETE
FROM people
WHERE id IN (
  SELECT id
  FROM (
      SELECT id, name, email,
             row_number() over (PARTITION BY email ORDER BY id DESC) AS row_num
      FROM people) d
  WHERE row_num > 1);



在執(zhí)行上面的語句之前，記得重新創(chuàng)建 people 表并生成測試數(shù)據(jù)。

基于多個(gè)字段的重復(fù)數(shù)據(jù)刪除方法和單個(gè)字段類似，大家可以自行嘗試。最后，為了防止再次產(chǎn)生重復(fù)數(shù)據(jù)，可以增加一個(gè)唯一約束。例如：

ALTER TABLE people ADD CONSTRAINT uk_people_email UNIQUE (email);



總結(jié)

本文介紹了如何在 Oracle 數(shù)據(jù)庫中查找并刪除重復(fù)記錄，包括使用聚合函數(shù)加上子查詢或者連接查詢、使用分析函數(shù)等方法查找單個(gè)字段或者多個(gè)字段中的重復(fù)數(shù)據(jù)，以及使用子查詢中分析函數(shù)等方法實(shí)現(xiàn)重復(fù)數(shù)據(jù)的刪除。

如果覺得文章對(duì)你有用，歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??！