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

#follow  user_id+follower_id
#follower_id user_id+music_id
#music id
#user_id

#用户已经喜欢的音乐
#用户的关注人喜欢的音乐

select
music_name
from
    (select
    distinct id
    ,music_name
    from
        (select
        music_id  #用户的关注人喜欢的音乐且自己未喜欢的音乐id
        from music_likes
        where user_id in (select
                        follower_id
                        from follow
                        where user_id = 1) #用户的关注人
        and music_id not in (select
                            music_id
                            from music_likes
                            where user_id = 1 ) #用户已经喜欢的音乐
                            ) t1 
    inner join music t2
    on t1.music_id = t2.id
    order by id)t3 #排序需求;