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

with as 创建临时表

1.先取出id=1的用户，他关注的人喜欢的音乐作为临时表

2.查询并过滤掉id=1的用户自身喜欢的音乐

WITH t AS 
(
    SELECT f.user_id, follower_id, music_id 
    FROM follow f
    LEFT JOIN music_likes ml
    ON f.follower_id = ml.user_id
    WHERE f.user_id = 1
)
SELECT music_name
FROM music 
WHERE id IN (SELECT music_id FROM t) AND id NOT IN (SELECT music_id FROM music_likes WHERE user_id = 1)