PSQL获得重复行 [英] PSQL get duplicate row

问题描述

我有这样的表 -

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.

这篇关于PSQL获得重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！