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

select 
music_name
from(
SELECT DISTINCT
  m.id, m.music_name
FROM follow f
JOIN music_likes t3
  ON t3.user_id = f.follower_id            -- 被关注者喜欢的歌
LEFT JOIN music_likes t2
  ON t2.user_id = 1 AND t2.music_id = t3.music_id  -- 只在“同一首歌”上反连接
JOIN music m
  ON m.id = t3.music_id
WHERE f.user_id = 1
  AND t2.music_id IS NULL                  -- 用户1没喜欢过
ORDER BY m.id ASC
)tmp;