SQL 如何删除重复行

在本节中，我们学习了在MySQL和Oracle中删除重复行的不同方法。如果SQL表中包含重复行，则必须删除重复行。

准备示例数据

脚本创建了一个名为contacts的表。

DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(25) NOT NULL, 
    email VARCHAR(210) NOT NULL,
    age VARCHAR(22) NOT NULL
);

在上表中，我们插入了以下数据。

INSERT INTO contacts (first_name,last_name,email,age) 
VALUES ('Kavin','Peterson','kavin.peterson@verizon.net','21'),
       ('Nick','Jonas','nick.jonas@me.com','18'),
       ('Peter','Heaven','peter.heaven@google.com','23'),
       ('Michal','Jackson','michal.jackson@aol.com','22'),
       ('Sean','Bean','sean.bean@yahoo.com','23'),
       ('Tom ','Baker','tom.baker@aol.com','20'),
       ('Ben','Barnes','ben.barnes@comcast.net','17'),
       ('Mischa ','Barton','mischa.barton@att.net','18'),
       ('Sean','Bean','sean.bean@yahoo.com','16'),
       ('Eliza','Bennett','eliza.bennett@yahoo.com','25'),
       ('Michal','Krane','michal.Krane@me.com','25'),
       ('Peter','Heaven','peter.heaven@google.com','20'),
       ('Brian','Blessed','brian.blessed@yahoo.com','20');
       ('Kavin','Peterson','kavin.peterson@verizon.net','30'),

我们在执行 DELETE语句后，运行脚本以重新创建测试数据。

查询从contacts表返回数据：

SELECT * FROM contacts
ORDER BY email;

以下SQL查询从联系人表中返回重复的电子邮件：

SELECT
    email, COUNT(email)
FROM
    contacts
GROUP BY
    email
HAVING
COUNT (email) > 1;

我们有三行带有 重复的 电子邮件。

（A）使用DELETE JOIN语句删除重复行

DELETE t1 FROM contacts t1
INNERJOIN contacts t2 
WHERE
    t1.id < t2.id AND
    t1.email = t2.email;

输出：

Query OK, three rows affected (0.10 sec)

三行已删除。我们执行下面的查询，查找表中的 重复电子邮件 。

SELECT
    email, 
COUNT (email)
FROM
    contacts
GROUP BY
    email
HAVING
COUNT (email) > 1;

查询返回的是空集。要验证来自联系人表的数据，请执行以下SQL查询：

SELECT * FROM contacts;

行 id’s 9，12和14 已被删除。我们使用下面的语句删除重复的行：

执行该脚本以 创建 联系人。

DELETE c1 FROM contacts c1
INNERJ OIN contacts c2 
WHERE
    c1.id > c2.id AND
    c1.email = c2.email;

(B) 使用中间表删除重复行

使用中间表删除重复行的步骤如下：

步骤1： 创建一个与实际表相同的新表 结构 ：

CREATE TABLE source_copy LIKE source;

步骤2 . 将数据库原始调度的不同行插入：

INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; 

步骤3 . 删除原表并将立即表重命名为原表。

DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;

例如，下面的语句从联系人表中删除 重复的 电子邮件所对应的 行 ：

-- step 1
CREATE TABLE contacts_temp
LIKE contacts;

-- step 2
INSERT INTO contacts_temp
SELECT * FROM contacts 
GROUP BY email;

-- step 3
DROP TABLE contacts;

ALTER TABLE contacts_temp
RENAME TO contacts;

使用ROW_NUMBER()函数删除重复行

注意：ROW_NUMBER()函数从MySQL版本8.02开始支持，因此在使用该函数之前应该检查我们的MySQL版本。

下面的语句使用 ROW_NUMBER() 函数为每一行分配一个连续的整数。如果电子邮件重复，该行的值将大于1。

SELECT id, email, ROW_NUMBER() 
OVER (PARTITION BY email 
ORDER BY email
    ) AS row_num
FROM contacts;

以下SQL查询返回重复行的 id列表 ：

SELECT id
FROM (SELECT id,
ROW_NUMBER() OVER (
PARTITION BY email ORDER BY email) AS row_num
FROM
contacts
) t
WHERE
row_num> 1;

输出：

id   
---  
9   
12   
14   

在Oracle中删除重复记录

当我们在表中发现重复记录时，我们必须删除不需要的副本，以保持我们的数据干净和唯一。如果一个表有重复的行，我们可以使用 DELETE 语句来进行删除。

在这种情况下，我们有一个列，它不是用来评估表中重复记录的 group 的一部分。

考虑下面给出的表：

-- create the vegetable table
CREATE TABLE vegetables (
VEGETABLE_ID NUMBER generated BY DEFAULT AS ID ENTITY,
VEGETABLE_NAME VARCHAR2(100),
color VARCHAR2(20),
        PRIMARY KEY (VEGETABLE_ID)
);

-- insert sample rows
INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Potato','Brown');
INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Potato','Brown');
INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Onion','Red');
INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Onion','Red');
INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Onion','Red');
INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Pumpkin','Green');
INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Pumpkin','Yellow');

-- query data from the vegetable table
SELECT * FROM vegetables;

假设我们想保留具有最高 VEGETABLE_ID 的行，并删除所有其它的副本。

SELECT
MAX (VEGETABLE_ID)
FROM
vegetables
GROUP BY
VEGETABLE_NAME,
color
ORDER BY
MAX(VEGETABLE_ID);

MAX(VEGETABLE_ID)   
---  
2   
5   
6   
7   

我们使用 DELETE 语句来删除 VEGETABLE_ID COLUMN 列中值不是最高的行。

DELETE FROM
vegetables
WHERE
VEGETABLE_IDNOTIN
  (
SELECT
MAX(VEGETABLE_ID)
FROM
vegetables
GROUP BY
VEGETABLE_NAME,
color
);

三行已被删除。

SELECT *FROM vegetables;

如果我们想保留具有最低id的行，请使用 MIN() 函数而不是 MAX() 函数。

DELETE FROM
vegetables
WHERE
VEGETABLE_IDNOTIN
  (
SELECT
MIN(VEGETABLE_ID)
FROM
vegetables
GROUP BY
VEGETABLE_NAME,
color
  );

以上方法适用于我们有一个不属于用于评估重复项的组的列的情况。如果在列中的所有值都有副本，那么我们无法使用 VEGETABLE_ID 列。

让我们删除并创建 vegetable 表，并使用新的结构。

DROP TABLE vegetables;
CREATE TABLE vegetables (
VEGETABLE_ID NUMBER,
VEGETABLE_NAME VARCHAR2(100),
Color VARCHAR2(20)
);

INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(1,'Potato','Brown');
INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(1, 'Potato','Brown');
INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color)VALUES(2,'Onion','Red');
INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color)VALUES(2,'Onion','Red');
INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(2,'Onion','Red');
INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(3,'Pumpkin','Green');
INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES('4,Pumpkin','Yellow');

SELECT * FROM vegetables;

在蔬菜表中，所有列中的值 VEGETABLE_ID, VEGETABLE_NAME ，和 color 已被复制。

我们可以使用 rowid ，一个指示 Oracle 存储行的定位器。因为 rowid 是唯一的，所以我们可以使用它来删除重复的行。

DELETE
FROM
Vegetables
WHERE
rowed NOT IN
(
SELECT
MIN(rowid)
FROM
vegetables
GROUP BY
VEGETABLE_ID,
VEGETABLE_NAME,
color
  );

查询验证删除操作：

SELECT * FROM vegetables;