九月 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;
正如您所看到的,basket
表中有一些重复的行,例如 2 个苹果 (apple) 和 3 个橙子 (orange) 。
查找重复行
如果表的行数很少,您可以立即看到哪些行是重复的。然而,大表却并非如此。
要查找重复行,可以使用以下语句:
SELECT
fruit,
COUNT( fruit )
FROM
basket
GROUP BY
fruit
HAVING
COUNT( fruit )> 1
ORDER BY
fruit;
使用 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;
正如您所看到的,该语句删除了 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;
结果:
完美!具有最低 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
列中具有重复值的所有行。
使用新建表删除重复行
要使用新建表删除行,请使用以下步骤:
下面说明了从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 表中的重复行。