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

# 明确问题：查询向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