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

首先想到用联结，但是时间和压力下还是运用子查询将复杂问题分解为小问题。实际数据量大时，子查询的效率比联结效率低，in和exist在面对不同大小的内外表时效率也不同。

select music_name from music 
where id in 
(
    select music_id from music_likes where user_id 
    in (select follower_id from follow where user_id = 1) #找到用户喜爱的用户
    and music_id not in 
    (select music_id from music_likes where user_id = 1) #排除用户已经喜欢的音乐
) 
order by id;

使用连接

select b.music_name from music_likes a left join music b 
on a.music_id = b.id
where a.user_id in
(select follower_id from follow where user_id = 1) 
and 
a.music_id not in
(select music_id from music_likes where user_id = 1)
order by a.music_id asc;