PostgreSQL UNION只对特定列忽略重复

I have posts table and notifications table. I want the posts which are also in notifications table to be scored as 999999 instead of their orijinal score. The following query gives the result in the picture. Red's are unionized by notification and the rest are from posts. As you can see even though they are unionized blues are duplicate since the score is different UNION doesn't count them as duplicates. How can I make them count as duplicates and only allow red ones.

我有贴子表和通知表。我希望通知表中的帖子的分数是99999999，而不是他们所在的地区的分数。下面的查询给出了图片中的结果。红色的是由通知组成的，其余的则来自于帖子。正如你所看到的，即使他们是工会化的布鲁斯也是重复的，因为不同的工会不把他们算作重复的。我怎么能把它们算作复制品而只允许红色的呢?

UNION and UNION ALL gives the same result since I'm changing the objects score value for the notification part of the union. I tried DISTINCT ON (id) but it doesn't allow to be sorted over score and DISTINCT ON (id,score) sorts it wrong.

UNION和UNION都给出相同的结果，因为我正在更改UNION的通知部分的对象score值。我尝试了在(id)上进行排序，但是不允许在(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

Is there a way to allow UNION check duplications only id column?

是否有一种方法允许UNION check duplicate only id列?

1 个解决方案

#1

You can use CTE:

您可以使用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 ...