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

WITH
    people as (
        select
            follower_id
        from
            follow
        where
            user_id = 1
    ),
    likes as (
        select
            music_id
        from
            music_likes
        where
            user_id=1
    ),
    people_like as (
        select distinct
            music_id
        from
            people p
            left join music_likes m on p.follower_id = m.user_id
        where
            music_id not in (select music_id from likes)
    )

select
    m.music_name
from
    people_like p 
    join music m on p.music_id=m.id 
order by
    m.id 

music_id not in (select music_id from likes)去除集合的交集部分