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

#user_id=1 不要推荐该用户已经喜欢的音乐，从歌单中剔除

with new_follow as
(
select
*
from follow 
where user_id =1
)#只有user_id=1的关注人
, new_music_likes as
(
select
*
from music_likes
where music_id not in (select music_id from music_likes where user_id =1)
)#歌单中不存在User_id=1喜欢的歌
,new_music as
(
select
*
from music
where id not in (select music_id from music_likes where user_id =1)
)#歌单名中不存在User_id=1喜欢的歌

select
#distinct m.music_name as music_name #二刷：缺distinct，没考虑到关注的不同人喜欢同一首歌的情况，二刷.1 出现distinct后order by无distinct临时表里面的字段的情况
music_name
from new_follow f left join  new_music_likes l
on f.follower_id = l.user_id #存在关注的人对应l表里面的音乐是Null的情况
left join new_music m
on l.music_id=m.id
where l.music_id is not null
#order by m.id asc
group by m.music_name, m.id
order by m.id asc