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

with music_id_t as (
    # 找出id为1的人关注的人喜欢听的歌
    select
    music_id
    from music_likes 
    where user_id in (
        # 找出userid为1的用户关注的人的id
        select
        follower_id
        from follow 
        where user_id = 1
    )
    and music_id not in (
        # 找出id为1的人喜欢的音乐
        select
        music_id
        from music_likes
        where user_id = 1
    )
)
select
music_name
from music 
# 内连接筛选出不属于id为1的用户已经喜欢的歌曲
inner join music_id_t on music.id = music_id_t.music_id
# 去重
group by music_name,id
# 降序
order by id