PostgreSQL 教程: 删除重复行

九月 16, 2023

摘要：在本教程中，您将学习如何使用各种技术来删除 PostgreSQL 表中的重复行。

准备样例数据

首先，创建一个新表，名为basket，用于存储水果信息：

CREATE TABLE basket(
    id SERIAL PRIMARY KEY,
    fruit VARCHAR(50) NOT NULL
);

其次，在basket表中插入一些水果。

INSERT INTO basket(fruit) values('apple');
INSERT INTO basket(fruit) values('apple');

INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');

INSERT INTO basket(fruit) values('banana');

第三步，从basket表中查询数据：

SELECT
    id,
    fruit
FROM
    basket;

fruit table

正如您所看到的，basket表中有一些重复的行，例如 2 个苹果 (apple) 和 3 个橙子 (orange) 。

查找重复行

如果表的行数很少，您可以立即看到哪些行是重复的。然而，大表却并非如此。

要查找重复行，可以使用以下语句：

SELECT
    fruit,
    COUNT( fruit )
FROM
    basket
GROUP BY
    fruit
HAVING
    COUNT( fruit )> 1
ORDER BY
    fruit;

img

使用 DELETE USING 语句删除重复行

以下语句使用DELETE USING语句删除重复行：

DELETE FROM
    basket a
        USING basket b
WHERE
    a.id < b.id
    AND a.fruit = b.fruit;

在此示例中，我们将basket表与其自身连接起来，并检查两个不同的行 (a.id < b.id) 在fruit列中是否具有相同的值。

让我们再次查询basket表来验证重复的行是否被删除：

SELECT
	id,
	fruit
FROM
	basket;

delete duplicate rows in postgresql example - keep highest id

正如您所看到的，该语句删除了 id 最低的重复行并保留 id 最高的行。

如果要保留 id 最小的重复行，只需翻转WHERE子句中的运算符即可：

DELETE  FROM
    basket a
        USING basket b
WHERE
    a.id > b.id
    AND a.fruit = b.fruit;

为了检查该语句是否正确运行，我们来验证一下basket表中的数据：

SELECT
    id,
    fruit
FROM
    basket;

结果：

delete duplicate rows in postgresql example - keep lowest id

完美！具有最低 ID 的重复行保留了下来。

使用子查询删除重复行

以下语句使用子查询删除重复行并保留 id 最小的行。

DELETE FROM basket
WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY fruit
        ORDER BY  id ) AS row_num
        FROM basket ) t
        WHERE t.row_num > 1 );

在此示例中，子查询返回重复组中除第一行之外的重复行。外部DELETE语句删除了子查询返回的重复行。

如果要保留具有最高 id 的重复行，只需更改子查询中的结果排序顺序：

DELETE FROM basket
WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY fruit
        ORDER BY  id DESC ) AS row_num
        FROM basket ) t
        WHERE t.row_num > 1 );

如果您想根据多列的值删除重复项，这里是查询模板：

DELETE FROM table_name
WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY column_1,
         column_2
        ORDER BY  id ) AS row_num
        FROM table_name ) t
        WHERE t.row_num > 1 );

在这种情况下，该语句将删除column_1和column_2列中具有重复值的所有行。

使用新建表删除重复行

要使用新建表删除行，请使用以下步骤：

  1. 创建一个新表，它与应删除重复行的表具有相同的结构。
  2. 将不同的行从源表插入到新建表中。
  3. 删除源表。
  4. 将新建表重命名为源表的名称。

下面说明了从basket表中删除重复行的步骤：

-- step 1
CREATE TABLE basket_temp (LIKE basket);

-- step 2
INSERT INTO basket_temp(fruit, id)
SELECT 
    DISTINCT ON (fruit) fruit,
    id
FROM basket; 

-- step 3
DROP TABLE basket;

-- step 4
ALTER TABLE basket_temp 
RENAME TO basket;

在本教程中，您学习了如何使用DELETE USING语句、子查询和新建表的技术，删除 PostgreSQL 表中的重复行。

搜索

分类

标签