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的记录。