postgresql中的重复行问题

Posted on

问题描述

本文主要描述使用各种技术来删除PostgreSQL中的重复行。

准备示例数据

首先创建一个表：

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

接下来插入一些数据：

1
2
3
4
5
6
7
8
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');

查找重复行

1
2
3
4
5
6
7
8
9
10
11
SELECT
    fruit,
    COUNT( fruit )
FROM
    basket
GROUP BY
    fruit
HAVING
    COUNT( fruit )> 1
ORDER BY
    fruit;

使用 DELETE USING 语句删除重复的行

1
2
3
4
5
6
7
DELETE
FROM
    basket a
        USING basket b
WHERE
    a.id < b.id
    AND a.fruit = b.fruit;

使用子查询删除重复的行

1
2
3
4
5
6
7
8
9
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 );

ON和USING和NATURAL

ON子句是最常见的连接条件的类型：它接收一个和WHERE 子句相同的布尔表达式。如果两个分别来自T1和T2 的行在ON表达式上运算的结果为真，那么它们就算是匹配的行。

USING是一个连接条件的缩写语法：它接收一个用逗号分隔的字段名列表，这些字段必须是连接表共有的并且其值必须相同。最后，JOIN USING 会将每一对相等的输入字段输出为一个字段，其后跟着所有其它字段。因此，USING (a, b, c) 等效于ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了ON，那么在结果里a, b和c 字段都会有两个，而用USING的时候就只会有一个（如果使用了SELECT * 的话，他们会优先发生）。

最后，NATURAL是USING的缩写形式：它自动形成一个由两个表中同名的字段组成的USING列表(同名字段只出现一次)。如果没有同名的字段，NATURAL的行为会像CROSS JOIN。