pgsql删除某些列（不是全部）重复的行

Question

表-col_pk、col1、col2、col3、col4、col_date_更新

此表中有些行的col2和col3列值重复。我想保持那些列的列更新为最新（max）。

如：

col_pk, col1, col2,  col3, col4,   col_date_updated
1,      A,    hello, now,  200.00, 2017-12-12 15:09:44.437546
2,      B,    hello, now,  490.00, 2017-12-12 15:09:42.437065
3,      C,    hi,    now,  300.00, 2017-12-12 15:09:41.436617
4,      D,    hello, now,  250.00, 2017-12-12 15:09:45.436617
5,      E,    hi,    now,  250.00, 2017-12-12 10:09:41.436617

预期结果：

col_pk, col1, col2,  col3, col4,   col_date_updated
3,      C,    hi,    now,  300.00, 2017-12-12 15:09:41.436617
4,      D,    hello, now,  250.00, 2017-12-12 15:09:45.436617

Answer 1

你可以试试这样的。

SELECT t.*
  FROM yourtable t
 WHERE col_date_updated IN (SELECT MAX (col_date_updated)
                    FROM yourtable i
                   WHERE t.col2 = i.col2 AND t.col3 = i.col3);

所以，如果你想删除其他记录，你可以使用这个。

DELETE 
  FROM yourtable t
 WHERE col_date_updated NOT IN (SELECT MAX (col_date_updated)
                    FROM yourtable i
                   WHERE t.col2 = i.col2 AND t.col3 = i.col3);

演示

Answer 2

如果您只想选择获得预期的输出，那么行数就很方便了：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY col2, col3
        ORDER BY col_date_updated DESC) rn
    FROM yourTable
)
SELECT col_pk, col1, col2,  col3, col4, col_date_updated
FROM cte
WHERE rn = 1;

如果您想删除其他记录，那么我们也可以重用CTE：

DELETE FROM yourTable WHERE col_pk IN (SELECT col_pk FROM cte WHERE rn > 1);

Answer 3

看看这个。

SELECT DISTINCT ON (col2, col3) t.*
FROM table t
ORDER BY col_date_updated DESC

在col2和col3上应用不同的应用，因为你希望它们独一无二，并保持最新的订单