根據條件合併重複項

我在 PostgreSQL 數據庫中有下表

id | value_1 | value_2
-----------------------------
1  | 100     | 10
1  | NULL    | 20
2  | 100     | 20
3  | 100     | 12
4  | 100     | 16
5  | NULL    | 200
5  | 400     | 200
5  | 580     | 200

我想保留具有給定最大值的記錄value_1並刪除所有其他重複的記錄（具有相同的id），以便查詢將表減少到：

id | value_1 | value_2
-----------------------------
1  | 100     | 10
2  | 100     | 20
3  | 100     | 12
4  | 100     | 16
5  | 580     | 200

這可以使用 PostgreSQL 的（專有）distinct on ()運算符輕鬆完成：

select distinct on (id) *
from the_table
order by id, value_1 desc nulls last;

假設：

• 你想DELETE從表中實際複製。
• id被定義NOT NULL。
• value_1每個 distinct至少有一個 not-null id。
• 只有少數重複被刪除。

DELETE FROM tbl t0
WHERE  EXISTS (
  SELECT FROM tbl t1
  WHERE  t1.id = t0.id
  AND   (t1.value_1 > t0.value_1  -- a row with bigger value exists
      OR t0.value_1 IS NULL)      -- or NULL 
  );

在這種情況下，索引(id)甚至(id, value_1)會有所幫助。有關的：

• 從小表中刪除重複行
• 如何刪除重複條目？

清理後，UNIQUE索引(id)會保持這種狀態。（加上製作它NOT NULL。）

如果我的任何假設不成立（更具體！），可能需要一個不同的解決方案。

引用自：https://dba.stackexchange.com/questions/305840