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

with 1_follows_like as (
    select
        f.user_id,
        ml.music_id
    from 
        follow as f
    left join 
        music_likes as ml
    on 
        f.follower_id = ml.user_id
    where 
        f.user_id = 1
),

result as (
    select
        distinct 1fl.music_id,
        m.music_name
    from 
        1_follows_like as 1fl
    left join 
        music as m
    on 
        1fl.music_id = m.id
    where 
        music_id not in (select music_id from music_likes where user_id = 1)
    order by 
        1fl.music_id
)

select
    music_name
from    
    result
order by 
    music_id