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

将所有表联结，注意a1.m1n is null这个条件

with a as(
select m1.id m1d,m1.music_name m1n,m2.id m2d,m2.music_name m2n from
follow f left join music_likes ml1 on f.user_id=ml1.user_id
left join music_likes ml2 on f.follower_id=ml2.user_id
left join music m1 on ml1.music_id=m1.id
left join music m2 on ml2.music_id=m2.id
where f.user_id=1)
select music_name from
(select distinct m2d,m2n music_name from a left join (select distinct m1n from a) as a1 on m2n=a1.m1n
 where a1.m1n is null
order by m2d)a2