PostgreSQL UNION忽略特定列的重复项

Question

我有一个帖子表和通知表。我希望那些同时出现在通知表中的帖子，其分数为999999而不是原始分数。以下查询给出了图片中的结果。红色部分是由通知合并的，其余部分来自帖子。正如您所看到的，尽管它们被合并了，但蓝色部分是重复的，因为分数不同， UNION 不将它们视为重复项。如何使它们被视为重复项并只允许红色部分？

UNION 和 UNION ALL 由于我正在为联合的通知部分更改对象的分数值，因此会得到相同的结果。我尝试了 DISTINCT ON (id)，但它不允许按分数排序，而 DISTINCT ON (id, score) 排序不正确。

SELECT id, created_at, data, (data->>'score')::NUMERIC AS score
FROM posts
WHERE NOT EXISTS (
    SELECT 1
    FROM swipes
    WHERE ((swipes.data)->>'post_id')::INT = posts.id
      AND ((swipes.data)->>'user_id')::INT = 32)

UNION

SELECT DISTINCT ON (id) ((notifications.data)->>'post_id')::INT AS id, posts.created_at, posts.data, 9999999 AS score
FROM notifications, posts
WHERE ((notifications.data)->>'user_id')::INT = 32 AND posts.id = ((notifications.data)->>'post_id')::INT

ORDER BY score DESC

有没有一种方法可以只允许UNION检查id列的重复项？

- Thellimist

Answer 1

您可以使用CTE：

with CTE as ( your query without order by )
Select id, created_at, data, max(score) as score
from cte
Group by id, create_at, data
Order by ...

- dani herrera