按连接列排序，但在另一个上使用不重复 [英] Order by join column but use distinct on another

问题描述

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

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

注意:shared_id与id相同.

Note: shared_id is the same as id.

如您所见，我得到了重复的结果，这是我不想要的.我从上一个问题中找到了这是由于在broadcasts.created_at上选择了与众不同.

As you can see I'm getting duplicate results, which I don't want. I found out from a previous question that this was due to selecting distinct on broadcasts.created_at.

我的问题是:如何修改此查询，以便仅根据其ID 返回唯一的歌曲，但仍按广播的顺序排序.created_at?

My question is: How do I modify this query so it will return only unique songs based on their id but still order by broadcasts.created_at?

推荐答案

尝试以下解决方案:

SELECT a.maxcreated, b.*
FROM
    (
        SELECT bb.song_id, MAX(bb.created_at) AS maxcreated
        FROM follows aa
        INNER JOIN broadcasts bb ON aa.station_id = bb.station_id
        WHERE aa.user_id = 2
        GROUP BY bb.song_id
    ) a
INNER JOIN songs b ON a.song_id = b.id
ORDER BY a.maxcreated DESC
LIMIT 18

FROM子选择检索用户跟随的所有电台广播的不同的song_id；它还会获取与每首歌曲相关的最新广播日期.我们必须将其包含在子查询中，因为我们必须在要从中选择的列上使用GROUP BY，并且无论工作站是什么，我们都只需要唯一的song_id和maxdate.

The FROM subselect retrieves distinct song_ids that are broadcasted by all stations the user follows; it also gets the latest broadcast date associated with each song. We have to encase this in a subquery because we have to GROUP BY on the columns we're selecting from, and we only want the unique song_id and the maxdate regardless of the station.

然后我们将外部查询中的结果连接到songs表，以获取与每个唯一song_id

We then join that result in the outer query to the songs table to get the song information associated with each unique song_id

这篇关于按连接列排序，但在另一个上使用不重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！