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

# 明确问题：查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。
# 不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项
# 首先我们要知道user_id = 1 的用户喜欢的音乐

•  (select c.id,c.music_name
•  from follow as a
• join music_likes as b
•  on a.user_id=b.user_id
•  join music as c
•  on b.music_id=c.id  and a.user_id= 1
• group by c.id,c.music_name) y

# 然后找出user_id = 1 关注的ID喜欢的音乐

• (select c.id,c.music_name
• from 
•  (select follower_id
•  from follow
•  where  user_id = 1 ) as a
• join music_likes as b 
• on a. follower_id=b.user_id
•  join music as c 
• on b.music_id=c.id) x

最后user_id = 1 关注的ID喜欢的音乐NOT IN user_id = 1 喜欢的音乐即可
SELECT
    music_name 
FROM
    (
    SELECT
        c.id,
        c.music_name 
    FROM
        ( SELECT follower_id FROM follow WHERE user_id = 1 ) AS a
        JOIN music_likes AS b ON a.follower_id = b.user_id
        JOIN music AS c ON b.music_id= c.id 
    GROUP BY
        c.id,
        c.music_name 
    ) x 
WHERE
    x.id NOT IN (
    SELECT
        c.id 
    FROM
        follow AS a
        JOIN music_likes AS b ON a.user_id = b.user_id
        JOIN music AS c ON b.music_id= c.id 
        AND a.user_id = 1 
    GROUP BY
        c.id 
    ) 
ORDER BY
    x.id