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

思路

1.分别找出用户1和用户1关注对象的喜爱的音乐

SELECT b.music_id
FROM follow a
INNER JOIN music_likes b
ON a.follower_id = b.user_id
AND a.user_id = 1

SELECT d.music_id
FROM follow c
INNER JOIN music_likes d
ON c.user_id = d.user_id
AND c.user_id = 1

2.判断关注对象喜爱的音乐NOT IN 用户喜欢的音乐

3.联结音乐表music

SELECT DISTINCT c.music_name
FROM follow a
INNER JOIN music_likes b
ON a.follower_id = b.user_id
AND a.user_id = 1
INNER JOIN music c
ON b.music_id = c.id
WHERE b.music_id NOT IN(SELECT d.music_id 
                        FROM follow c
                        INNER JOIN music_likes d
                        ON c.user_id = d.user_id
                        AND c.user_id = 1)
ORDER BY c.id;

ps.不加DISTINCT会出现重复的原因是查找用户及关注对象喜爱的音乐的时候，id列有可能有重复，此时没把重复项去除，查询出来的时候就会产生重复。