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

with t as(
    -- 每个用户喜欢的音乐名
    select
        ml.user_id,
        ml.music_id,
        m.music_name
    from music_likes as ml 
    join music as m on ml.music_id = m.id
)
select
    music_name
from t
where music_id not in  (select music_id from t where user_id = 1) -- 自己喜欢的音乐不要推荐
    and user_id in (select follower_id from follow where user_id = 1) -- 根据关注的人来推荐喜欢的音乐
group by music_id, music_name   -- 按音乐ID去重
order by music_id;