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

WITH T AS
(-- 用户喜欢的音乐名称
    SELECT 
        M0.*
        ,M1.*
    FROM music_likes M0
    LEFT JOIN music M1 ON M0.music_id = M1.id  
)
SELECT 
   T.music_name
FROM follow F 
LEFT JOIN T ON F.follower_id = T.user_id 
WHERE F.user_id = 1
    AND T.music_name NOT IN (SELECT music_name FROM T WHERE T.user_id = 1)
GROUP BY T.music_id 
ORDER BY T.music_id 
;