题解 | #网易云音乐推荐(网易校招笔试真题)#

with t1 as (
    SELECT DISTINCT music_name
    FROM follow
    JOIN music_likes 
        on follow.follower_id = music_likes.user_id
    JOIN music
        on music_likes.music_id = music.id
    WHERE follow.user_id = 1 and music_name NOT IN(
    SELECT music_name
    FROM music_likes
        JOIN music
            ON music_likes.music_id = music.id
    WHERE music_likes.user_id = 1
    )
)

SELECT t1.music_name
FROM t1
    JOIN music on t1.music_name = music.music_name
ORDER BY music.id

查询user_id = 1 的用户关注的人喜欢的音乐

SELECT music_name
    FROM music_likes
        JOIN music
            ON music_likes.music_id = music.id
    WHERE music_likes.user_id = 1

查询user_id = 1关注的人喜欢的音乐

FROM follow
    JOIN music_likes 
        on follow.follower_id = music_likes.user_id
    JOIN music
        on music_likes.music_id = music.id
    WHERE follow.user_id = 1

对音乐名进行去重

SELECT DISTINCT music_name

在对音乐名对应的id进行排序

SELECT t1.music_name
FROM t1
    JOIN music on t1.music_name = music.music_name
ORDER BY music.id

最终代码如下：

with t1 as (
    SELECT DISTINCT music_name
    FROM follow
    JOIN music_likes 
        on follow.follower_id = music_likes.user_id
    JOIN music
        on music_likes.music_id = music.id
    WHERE follow.user_id = 1 and music_name NOT IN(
    SELECT music_name
    FROM music_likes
        JOIN music
            ON music_likes.music_id = music.id
    WHERE music_likes.user_id = 1
    )
)

SELECT t1.music_name
FROM t1
    JOIN music on t1.music_name = music.music_name
ORDER BY music.id

住：ORDER BY 和 DISTINCT不能同时用，所以最后分成两张表联结排序