Delete Using 在postgresql中的应用

假设有一个表A,包含两个字段id和name,表结构如下:

1
2
3
4
5
6
test=# \d test;
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
name | character varying | | |

现在我们往表test中插入多条重复的数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
test=# insert into test values (1,'小明');
INSERT 0 1
test=# insert into test values (2,'小明');
INSERT 0 1
test=# insert into test values (3,'小明');
INSERT 0 1
test=# insert into test values (4,'小李');
INSERT 0 1
test=# insert into test values (5,'小李');
INSERT 0 1
test=# insert into test values (6,'小李');
INSERT 0 1
test=# insert into test values (7,'小红');
INSERT 0 1
test=# insert into test values (8,'小红');

插入后的结果如下表:

1
2
3
4
5
6
7
8
9
10
 id | name 
----+------
1 | 小明
2 | 小明
3 | 小明
4 | 小李
5 | 小李
6 | 小李
7 | 小红
8 | 小红

现在我们的需求是删除表test中重复的数据。

不使用delete using的写法

常规的写法我们需要借助ROW_NUMBER函数,先根据name字段进行划分,根据id进行倒序排列,取其ROW_NUMBER值大于1的,将其删除,剩余就是我们需要的值。

1
2
3
delete from test where id in (
select id from
(select id,name,ROW_NUMBER() over (partition by name order by id desc) as num from test ) as t where t.num >1);

使用delete using的写法

postgresql特有的一种写法就是使用delete using语法。

1
delete from test a using test b where a.id<b.id and a.name=b.name;

using 有点类似子查询,可以关联包含在where子语句中的字段的表。本例子中的a.id<b.id就是筛选出a表中id小于b表中id的记录。