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

 # 请你编写一个SQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。
 # 不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项
 # 思路:
 # 1.先在follow表中查询user_id为1的人关注的用户是谁
 SELECT follower_id
 FROM follow
 WHERE user_id = 1;
 # 1.1 找到user_id为1的用户喜欢的音乐music_id
 SELECT music_id FROM music_likes WHERE user_id = 1;
 # 2.通过上一步查出的用户id关联music_likes表，查看他们喜欢的音乐id是多少,根据1.1查出的music_id
 # 排除user_id喜欢的音乐
 WITH tmp_f_id AS(
     SELECT follower_id
     FROM follow
     WHERE user_id = 1
 )
 SELECT music_id
 FROM tmp_f_id t JOIN music_likes t1 ON t.follower_id = t1.user_id
 WHERE music_id NOT IN (SELECT music_id FROM music_likes WHERE user_id = 1);
# 3.关联music表,不同人喜欢的音乐可能重复所以需要用distinct，展示查询出的结果
WITH tmp_f_id AS (
    SELECT follower_id
    FROM follow
    WHERE user_id = 1
),
     tmp_music_id AS (
         SELECT t1.music_id
         FROM tmp_f_id t
                  JOIN music_likes t1 ON t.follower_id = t1.user_id
         WHERE t1.music_id NOT IN (SELECT music_id
                                FROM music_likes
                                WHERE user_id = 1)
     )
SELECT DISTINCT mn.music_name
FROM tmp_music_id tm
         JOIN music mn ON tm.music_id = mn.id
ORDER BY mn.id;