Postgresql - SQL - 搜索表内重复行

今天写一个查询，需要查询表内某两列重复的数据，随之想到了删除重复行。这个在前几年的被面试中，也被提问过。做面试官的时候也问过别人。

算是一个比较基础的内容吧。

如果需要查询数据，只需要将列分组(group by)，对查询结果做计数(count)，大于1的就是重复的行，

 

如何删除重复行：

Oracle中，众所周知，存在一个虚拟列叫做row_num，我们可以删除小的或者大的row_num，这样，就只保留了一个。

那PG中呢，PG中也有一个虚拟列，叫做ctid，同样，我们可以删除大的或者小的ctid，只保留一个。

 

看SQL最直观，直接上SQL：

环境PG 11 beta 3:

 

test=# select * from test1031;

id | col1 | col2 | col3

----+------+------+------

1 | a | b | c

1 | a | a | c

2 | a | b | c

1 | a | b | c

3 | c | c | c

(5 rows)

 

查询表内col1, col2, col3 重复的行

test=# select col1, col2, col3, count(*) from test1031 group by col1, col2, col3 having count(*) > 1;

col1 | col2 | col3 | count

------+------+------+-------

a | b | c | 3

(1 row)

 

查询表内id, col1, col2, col3 重复的行

test=# select id, col1, col2, col3, count(*) from test1031 group by id, col1, col2, col3 having count(*) > 1;

id | col1 | col2 | col3 | count

----+------+------+------+-------

1 | a | b | c | 2

(1 row)

 

那我们删除重复的行。

查询要删除的行

test=# select * from test1031 where ctid not in (select max(ctid) from test1031 group by id, col1, col2, col3);

id | col1 | col2 | col3

----+------+------+------

1 | a | b | c

(1 row)

 

删除

test=# delete from test1031 where ctid not in (select max(ctid) from test1031 group by id, col1, col2, col3);

DELETE 1

 

查看重复行：

test=# select id, col1, col2, col3, count(*) from test1031 group by id, col1, col2, col3 having count(*) > 1;

id | col1 | col2 | col3 | count

----+------+------+------+-------

(0 rows)