PSQL获得重复行

本文介绍了PSQL获得重复行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的表 -

id object_id product_id 1 1 1 2 1 1 4 2 2 6 3 2 7 3 2 8 1 2 9 1 1

我想删除除这些以外的所有行 -

I want to delete all rows except these-

1 1 1 4 2 2 6 3 2 9 1 2

基本上有重复的，我想删除它们，但是保持一个副本不变。

Basically there are duplicates and I want to remove them but keep one copy intact.

这是最有效的方式？

推荐答案

如果这是一次性的话，那么你可以简单地确定要保留的记录如下：

If this is a one-off then you can simply identify the records you want to keep like so:

SELECT MIN(id) AS id FROM yourtable GROUP BY object_id, product_id;

你想检查这个工作，然后再做下一件事，实际上会抛出记录。要实际删除您所做的重复记录：

You want to check that this works before you do the next thing and actually throw records out. To actually delete those duplicate records you do:

DELETE FROM yourtable WHERE id NOT IN ( SELECT MIN(id) AS id FROM yourtable GROUP BY object_id, product_id );

MIN（id）显然总是返回一组（ object_id ， product_id ）的最低ID的记录。根据需要进行更改。

The MIN(id) obviously always returns the record with the lowest id for a set of (object_id, product_id). Change as desired.