Postgres:在列上选择Distinct不会通过Joins返回不同的结果

本文介绍了Postgres:在列上选择Distinct不会通过Joins返回不同的结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在建立一个包含以下表格的系统:

I'm building a system in which there are the following tables:

• 歌曲
• 广播
• 站
• 关注
• 用户

用户关注电台，电台通过广播播放歌曲.

A user follows stations, which have songs on them through broadcasts.

我正在根据用户关注的电台为他们构建歌曲的供稿".

I'm building a "feed" of songs for a user based on the stations they follow.

以下是查询:

SELECT DISTINCT ON ("broadcasts"."created_at", "songs"."id") songs.* FROM "songs" INNER JOIN "broadcasts" ON "songs"."shared_id" = "broadcasts"."song_id" INNER JOIN "stations" ON "broadcasts"."station_id" = "stations"."id" INNER JOIN "follows" ON "stations"."id" = "follows"."station_id" WHERE "follows"."user_id" = 2 ORDER BY broadcasts.created_at desc LIMIT 18

正如您在此处看到的那样，我收到的重复歌曲正在发生，因为我正在跟踪两个都广播了同一首歌曲的电台:

As you can see here, I'm getting duplicate songs which is happening because I'm following two stations that BOTH have broadcasted that same song:

注意:shared_id与id相同.

Note: shared_id is the same as id.

我的问题是:如何修改此查询，使其仅根据其ID(或shared_id)返回唯一的歌曲?

推荐答案

从DISTINCT谓词中删除"broadcasts"."created_at"，因为它返回非唯一值，因此重复了songs.如果您需要使用此信息进行排序，请创建一个新问题.

Remove "broadcasts"."created_at" from the DISTINCT predicate because it is returning non-unique values, hence duplicate songs. If you need to use this information to sort please create a new question.