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

with follow_music as(
select
a.user_id as uid,
a.follower_id as fid,
b.music_id as fmid
from follow  as a
left join music_likes as b
on a.follower_id = b.user_id
where a.user_id = 1
)
select
music_name
from 
(select distinct
c.fmid,
e.music_name as music_name
from follow_music  as c
left join music_likes as d
on c.uid = d.user_id 
and c.fmid = d.music_id
left join music as e
on c.fmid = e.id
where d.music_id is null) as t1
order by t1.fmid  -- 用内层带出来的 fmid 排序