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

# 给1推荐他关注的人所喜欢的音乐
with
t1 as(
    select distinct
        music_id
    from
        follow as f
        left join music_likes as ml
        on f.follower_id=ml.user_id
    where
        f.user_id=1
),
t2 as(
    select
        music_id
    from
        music_likes
    where
        user_id=1
)

select
    music_name
from
    t1 left join music on t1.music_id=music.id
where
    not exists(
        select 1 from t2 where t1.music_id=t2.music_id
    )
order by
    music.id