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

/*select distinct music_name
from(select f.user_id,f.follower_id,ml.music_id,music_name
from follow f right join music_likes ml on f.follower_id=ml.user_id left join music m on ml.music_id=m.id
where f.user_id=1) t1 inner join music_likes t2 on t1.user_id=t2.user_id
where t1.music_id<>t2.music_id*/ #莫名其妙的错

/*select music_name
from follow t1 join music_likes t2 on follower_id=t2.user_id
join music t3 on t2.music_id=t3.id
where  t1.user_id=1 and t3.music_name not in (
select music_name
from music_likes t2 join music t3 on t2.music_id=t3.id
where t2.user_id=1)
group by t2.music_id
order by t2.music_id*/

/*select music_name
from(SELECT DISTINCT m.id, m.music_name
FROM follow f
JOIN music_likes ml ON f.follower_id = ml.user_id
JOIN music m ON ml.music_id = m.id
WHERE f.user_id = 1 AND m.id NOT IN (
    SELECT music_id FROM music_likes WHERE user_id = 1
)
ORDER BY m.id ASC) t;*/

select music_name
from(SELECT  m.music_name,m.id
FROM follow f
JOIN music_likes ml ON f.follower_id = ml.user_id
JOIN music m ON ml.music_id = m.id
WHERE f.user_id = 1 AND m.id NOT IN (
    SELECT music_id FROM music_likes WHERE user_id = 1
)
GROUP BY m.music_name,m.id
ORDER BY m.id ASC) t;