题解 | #网易云音乐推荐(网易校招笔试真题)#
网易云音乐推荐(网易校招笔试真题)
http://www.nowcoder.com/practice/048ed413ac0e4cf4a774b906fc87e0e7
先使用子查询找到用户1关注的人喜欢的所有音乐
SELECT t.music_id FROM follow AS t LEFT JOIN music_likes AS t1 ON t.follower_id = t1.user_id WHERE t.user_id = 1
然后和通过where语句筛选掉该用户已经喜欢的音乐
WHERE e.music_id not IN (SELECT music_id FROM music_likes WHERE user_id = 1 )
SELECT DISTINCT n.music_name FROM (-- 找出user_1关注的人喜欢的所有音乐 SELECT t1.music_id FROM follow AS t LEFT JOIN music_likes AS t1 ON t.follower_id = t1.user_id WHERE t.user_id = 1 ) AS e JOIN music AS n ON e.music_id = n.id WHERE e.music_id not IN (SELECT music_id FROM music_likes WHERE user_id = 1 ) ORDER BY n.id
在牛客现在版本的mysql中不支持order by未被select的列，但是在sqlite中可以正常运行
