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

with music as(
    select
    ml.user_id,
    m.id,
    m.music_name
    from music_likes ml
    left join music m
    on ml.music_id = m.id
)
select music_name 
from (
select distinct
    m.music_name,
		m.id
from follow f
left join music m
on f.follower_id = m.user_id
where  f.user_id = 1
and m.id not in (select music_id from music_likes where user_id = 1 )) m
order by id

用一个cte窗口,进行用户和音乐的连接,生成一张,所有用户的喜好音乐表,接着再从用户关注表中选出用户1关注的人,并排除用户1自己喜欢的音乐