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

with 
    a as ( -- 自己喜欢的歌
        select distinct f.user_id,music_id
        from follow f
        inner join music_likes m on f.user_id = m.user_id
        where f.user_id = 1
    ),
    b as ( -- 关注的人喜欢的歌
        select distinct follower_id,music_id
        from follow f
        inner join music_likes m on f.follower_id = m.user_id -- 连接条件粗心写错过
        where f.user_id = 1
    ),
    c as (
        select * from music
    )

select music_name
from (
    select distinct b.music_id
    from a 
    right join b on a.music_id = b.music_id
    where a.music_id is null -- 筛选条件粗心写错过： b.music_id is null
) t1
inner join c on t1.music_id = c.id; -- 连接条件粗心写错过：c.music_id