题解 | #网易云音乐推荐(网易校招笔试真题)#
网易云音乐推荐(网易校招笔试真题)
https://www.nowcoder.com/practice/048ed413ac0e4cf4a774b906fc87e0e7
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
;