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

with t1 as(
select 
    t1.user_id,
    music_id as follow_music_id
from follow t1 left join music_likes t2 on t1.follower_id=t2.user_id 
where t1.user_id=1 
),
t2 as(
select 
    user_id,
    follow_music_id
from t1 
where follow_music_id not in (select  music_id from music_likes where user_id=1)
),
t4 as(
select 
    distinct follow_music_id,
     music_name 
from t2 inner join music on t2.follow_music_id = music.id 
order by follow_music_id
)
select  music_name from t4