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

SELECT a.music_name FROM (
SELECT DISTINCT a.music_name,a.music_id  FROM (
select a.*,b.music_id,c.music_name,COALESCE(d.music_id,0) AS user_like_music_id from 
follow a
left join music_likes b 
on a.follower_id=b.user_id
left join music c
on b.music_id=c.id
LEFT JOIN (SELECT a.music_id FROM music_likes a inner join music_likes b on a.music_id=b.music_id
INNER JOIN follow c on  a.user_id=c.user_id AND b.user_id=c.follower_id) d
ON c.id=d.music_id 
where a.user_id=1 )a WHERE a.music_id<>a.user_like_music_id GROUP BY a.music_id ORDER BY a.music_id) a