sql - MySQL:选择一行的出现，然后将结果与另一个查询组合

Question

我完全不知道应该怎么做。

我正在尝试显示前 100 首最受欢迎的歌曲列表。我有这部分可以找到最喜欢的 100 首歌曲:

SELECT like_song_id, COUNT(like_song_id)
AS occurances
FROM likes
GROUP BY like_song_id
ORDER BY occurances
DESC LIMIT 100;

效果很好，我得到了一个按正确顺序排列的热门歌曲列表。但我还需要结果中的其他信息，如下:

我需要在 song_id = like_song id 上加入“歌曲”表。我还需要再次对“喜欢”表进行左外连接(或其他操作)，以查看当前用户($user_id (PHP))是否已经喜欢前 100 名列表中的任何歌曲。

我该怎么做？

编辑:

这是对我有用的最后一个查询，谢谢!

SELECT *
FROM (
    SELECT like_song_id, COUNT(like_song_id) AS occurrences
    FROM likes
    GROUP BY like_song_id
    ORDER BY occurrences DESC
    LIMIT 100
) T1
JOIN songs ON songs.song_id = T1.like_song_id
LEFT OUTER JOIN likes ON likes.like_song_id = T1.like_song_id AND like_user_id = 1

ORDER BY occurrences DESC LIMIT 100

Answer 1

你可以尝试子选择:

SELECT *
FROM (
    SELECT like_song_id, COUNT(like_song_id) AS occurrences
    FROM likes
    GROUP BY like_song_id
    ORDER BY occurrences DESC
    LIMIT 100
) T1
JOIN songs ON songs.song_id = T1.like_song id
JOIN likes ON likes.like_song_id = T1.like_song id AND userid = 123