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

SELECT
    DISTINCT music_name  # 去重
FROM music m
JOIN(
    SELECT
        DISTINCT ml.user_id,
        music_id
    FROM music_likes ml
    JOIN(
        SELECT
            user_id,
            follower_id
        FROM follow
        WHERE user_id = 1 # 首先，找到user_id = 1的用户关注的人
    ) AS tb1  # tb1 找到user_id = 1或关注的人喜欢的music_id
    ON tb1.follower_id = ml.user_id OR ml.user_id = 1 # 我这里用的follower_id JOIN 了 ML的 user_id
    WHERE music_id NOT IN (SELECT    # 这里把用户已经喜欢的音乐给剔除出去
                                DISTINCT music_id
                            FROM follow f
                            JOIN music_likes ml
                            ON f.user_id = ml.user_id
                            WHERE ml.user_id = 1) # user_id = 1用户已经喜欢的音乐
    ORDER BY music_id ASC
) AS tb2 #tb2找到关注的人喜欢的音乐名
ON m.id = tb2.music_id